Querying an Access Database from an SP

Question

Post reply

Querying an Access Database from an SP

hawg

SSCertifiable

Points: 6044
More actions
January 6, 2004 at 7:19 am

#101834

I am writing a process that interacts with an Access database. I am able to do most of my work using DTS to move my data but there is one scenario I am working with that would be better if I could simply query the data directly with T-SQL.
I was doing some research on how this could be done and I saw some things about OPENROWSET and OPENDATASOURCE that look like they might work but I am missing a piece.
The database I am using can only be accessed using a special "System Database" (MDW). I cannot figure out how to include this MDW database in the arguments of these commands to use them.
If anyone knows of a way to query data directly from T-SQL, using a non-default system database (MDW), please let me know.
Also, I not only want to query the data, I would like to update the data as well. I don't think this should be a problem but I wanted to mention it.
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ThomasH SSCertifiable Points: 7437 More actions · Answer 1

Try

select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Accessdb.MDB';'admin';'',

'select * from AccessTable')

from within T-SQL

Once you understand the BITs, all the pieces come together

netguy Mr or Mrs. 500 Points: 546 More actions · Answer 2

Was wondering if ThomaH's response actually worked?

hawg SSCertifiable Points: 6044 More actions · Answer 3

Sorry for not replying sooner but anyway, ThomasH's suggestion did not work. The problem was that he didn't address the system database (mdw) requirement.

I have gotten my query to work using OPENDATASOURCE. The following is the command I use:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','User ID=DBUser;Data Source=C:\Temp\MyDB.MDB;Persist Security Info=False;Jet OLEDB:System database=C:\Temp\MySysDB.mdw')...TableName

This let me query everything from TableName. I am also able to use this for INSERT, UPDATE, and DELETE. I know this is not the most efficient method but it is the best method for my situation.

----------------------------------------------------------

01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 4

bellis

SSCarpal Tunnel

Points: 4667

January 7, 2004 at 7:08 am

#488947

Can you use linked servers for this task?

hawg SSCertifiable Points: 6044 More actions · Answer 5

I have been trying a linked server but the problem with this is the system database. According to Microsoft, a linked server that is access is going to use the default system database that is in the registry. They suggest changing the registry setting to use the system database you want. The problem with this is that it makes a global change for the whole box. The Access database I want to query is the only one that uses this particular system database so making a global change does not make sense.

----------------------------------------------------------

01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011

ThomasH SSCertifiable Points: 7437 More actions · Answer 6

Sorry about overlooking the "system" database thing ...

I just posted the way I use to access & update "basic" Access databases & tables.

Once you understand the BITs, all the pieces come together

scking SSCrazy Points: 2598 More actions · Answer 7

I used the OPENDATASOURCE successfully in the query analyzer and saved a view. When I try to open the view the following error is presented. I have also received the same error for the 'Microsoft.Jet.OLEDB.4.0' OLE DB provider. Is there a solution to this?

Could not create an instance of OLE DB provider 'MSDASC'

noeld SSC Guru Points: 96590 More actions · Answer 8

1. Goto this REgKey and update the path for the .mdw

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB

2. Create an Access linked Server

3. Set up Permissions on the Linked Server Security Tab

3. Use LinkedServerName...AccessTableName

4. If you use that a lot you may want to create Views for those tables and use them as regular tables

HTH

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 9

If you don't want to make it Global and I still don't know what's your problem with that?

You can

1.Create A SYSTEM DSN pointing to the mdb file

2. On the ODBC Data Source Administrator you can specify the .mdw and even the user name and password for the connection

3. Create the linked server using the OLEDB Provider for ODBC Drivers and enter DSN of previous step

4. Read my previous Post from 3 on.

HTh

* Noel

Bill Nye Hall of Fame Points: 3248 More actions · Answer 10

Not sure if this will help, but maybe it will provoke other ideas.

While trying to remember what a .mdw was for, I ran across this in Access help:

"The workgroup is defined by the workgroup information file, typically called "system.mdw". For users to gain access to the secured objects in your database, DAO must have the location of this workgroup information file. The location can be identified to DAO either by specifying it in the Windows Registry or by setting the SystemDB property. On setup, the default setting is simply "system.mdw" with no path".