| Author |
 Topic  |
|
|
sanjay_jadam
Yak Posting Veteran
India
53 Posts |
 Posted - 12/06/2005 : 08:54:53
|
How can i access data from MS-Access using SQL query in SQL Server 2000 ??
|
|
|
ditch
Flowing Fount of Yak Knowledge
South Africa
1417 Posts |
Posted - 12/06/2005 : 08:57:34
|
Books OnLine is your freind.
Look Up "OPENROWSET"
Duane.  |
 |
|
|
Plotin
Starting Member
USA
21 Posts |
Posted - 12/06/2005 : 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
USA
21 Posts |
Posted - 12/06/2005 : 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
USA
21 Posts |
Posted - 12/06/2005 : 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
Flowing Fount of Yak Knowledge
Nigeria
2702 Posts |
Posted - 12/08/2005 : 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 |
Edited by - afrika on 12/08/2005 07:10:15 |
|
|
|
yuemb
Starting Member
China
1 Posts |
Posted - 08/09/2007 : 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 Posts |
Posted - 09/04/2007 : 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
|
|
|
| |
Topic |
|
Insert data from Access to SQL server
