Insert data from Access to SQL server

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.

All Forums

Other Forums

MS Access

Insert data from Access to SQL server

Author

Topic

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2005-12-06 : 08:54:53

How can i access data from MS-Access using SQL query in SQL Server 2000 ??

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-12-06 : 08:57:34

Books OnLine is your freind.

Look Up "OPENROWSET"

Duane.

Plotin
Starting Member

21 Posts

Posted - 2005-12-06 : 11:27:56

Books Online includes a whole chapter called Distributed Queries on this topic

You can link Access to SQL Server using stored procedure sp_addlinkedserver as followed

sp_addlinkedserver 'ServerName', 'Access 95', 'Microsoft.Jet.OLEDB.4.0', 'C:\SomPath\SomeFile.mdb'

ServerName is the name you assign to the linked server
Access 95 specifies the version of Access
Microsoft.Jet.OLEDB.4.0 is the OLEDB driver
C:\SomPath\SomeFile.mdb is the path to the Access file you want to access

Once the file is linked, you can use sp_tables_ex or sp_columns_ex to check if the file has been linked properly.
These to stor procs give you info about the meta data of your Access file.

When the file has been linked correctly you are able run T-SQL statements against the linked server

You refer to a table in your Access file using a fully qualified identifier like
MyServer...MyTable

For Example using the assigned server name Access above and and imaginary table like Address a SELECT statement would look like this

Select *
From Access...MyTable

Once you are done using the linked server you drop it by using sp_dropserver SERVERNAME

One can use this technique also for linking Excel or Text files

Plotin

Plotin
Starting Member

21 Posts

Posted - 2005-12-06 : 11:30:45

Sorry, linking the Access file to SQL would require to put this into a stored procedure in order to use it in a query.

Plotin

Plotin
Starting Member

21 Posts

Posted - 2005-12-06 : 11:41:59

Just tried OpenRowSet but returned the following error

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Here is the T-SQL statement used

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MKBD.mdb';'admin';'mypwd', ArticleID)

Does someone know what to do?

Plotin

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-12-08 : 07:06:50

open the access file, then click open or Ctrl + O

then in the file types choose ODBC connection and point it to your SQL server app and corresponding table

Afrika

yuemb
Starting Member

1 Post

Posted - 2007-08-09 : 00:22:49

quote:
Originally posted by afrika

open the access file, then click open or Ctrl + O

then in the file types choose ODBC connection and point it to your SQL server app and corresponding table
Afrika

Is it right?I use the method, just create a new access file and quote several tables from my sqlserver."Point it to your SQL server app and corresponding table",what do yo mean,Should I point the file to what?I confused.
This topic ask how to access data from Ms-Access using Sql in Sql server.Do you reverse the intention?

---------
yuemb
Best Regards

Arghknork
Starting Member

1 Post

Posted - 2007-09-04 : 16:51:51

quote:
Originally posted by Plotin

Just tried OpenRowSet but returned the following error

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Here is the T-SQL statement used

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MKBD.mdb';'admin';'mypwd', ArticleID)

Does someone know what to do?

Plotin

The problem is your Access password on that database probably isn't 'mypswd'. If you have a password on the database, put your own in there, if not use this command ...

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MKBD.mdb';'admin';'', ArticleID)

Phil King

Phil King

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources