Post reply

Querying an Access Database from an SP

  • 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.

    ----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

  • January 6, 2004 at 8:21 am

    #488798

    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

  • January 7, 2004 at 6:14 am

    #488937

    Was wondering if ThomaH's response actually worked?

  • January 7, 2004 at 7:08 am

    #488948

    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.

    ----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

  • January 7, 2004 at 7:08 am

    #488947

    Can you use linked servers for this task?

  • January 7, 2004 at 7:18 am

    #488951

    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.

    ----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

  • January 7, 2004 at 7:32 am

    #488956

    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

  • January 7, 2004 at 10:13 am

    #489011

    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'

  • January 7, 2004 at 11:32 am

    #489023

    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

  • January 7, 2004 at 1:36 pm

    #489035

    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

  • January 7, 2004 at 3:43 pm

    #489060

    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".

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

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