Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
genius_palli
Starting Member
42 Posts |
 Posted - 2009-11-20 : 00:59:24
|
| Hi all..I am happy to post again, after a very long time.Friends I am looking for some piece of code through which I can import from an access database (.mdb file) to SQL Server.I am using SQL Server 2008 Express Edition. I am trying the following :-//==============SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="E:\\Shared Folder\\GCG-42\\GCG.mdb";User ID=Admin;Password=;')...[SubjectM2_prac]//===========But getting the below error :Msg 7314, Level 16, State 1, Line 1The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "SubjectM2_prac". The table either does not exist or the current user does not have permissions on that table.Please suggest what I am missing ?ThanksRegardsgenius_palli |
|
|
genius_palli
Starting Member
42 Posts |
Posted - 2009-11-20 : 02:17:00
|
| Hi all..The solution is :-1. Create a Linked Server connection with your Microsoft Access file(.mdb file).2. Fetch the records using Linked Server connection and also mention the table name(of Access file).3. Insert the data into any SQL table.The Code is :-EXEC sp_addlinkedserver@server = 'ConnectionName',@provider = 'Microsoft.Jet.OLEDB.4.0',@srvproduct = 'OLE DB Provider for Jet',@datasrc = '<Path of .mdb file>'SELECT * FROM <ConnectionName>...[TableName]Note :- Create a Linked Server once and then fetch the records as you wish.Note :- Three dots (...) is must and [TableName] is a table from Access file.Hope this helps.Regardsgenius_palli |
 |
|
|
|
|
|
Microsoft Access dbase to SQL Server dbase