Getting Access data into SQL server

  • Hi,

    I'm trying to connect a msaccess file located in another PC box to get some user data, I would like to make the connection using Linked Server.

    The database is secured, so I've followed the recomendations regading that:

    http://msdn2.microsoft.com/en-us/library/aa238103(SQL.80).aspx

    http://msdn2.microsoft.com/en-us/library/aa226395(SQL.80).aspx

    But when I configure the linked server I get some errors.

    As seen in the sql help, the sql string to get the linked server is:

    EXEC sp_addlinkedserver

    @server = 'MyServer',

    @provider = 'Microsoft.Jet.OLEDB.4.0',

    @srvproduct = 'OLE DB Provider for Jet',

    @datasrc = '\\192.168.4.185\MyShared\casj.mdb'

    I've tested using the .mdb file in a local folder to. But the the Access Workgroup Information file is not helping me with the login.

    I've found the following method that works in the Query Analyzer:

    Select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=C:\casj.mdb;Jet OLEDB: Database Password=test')...Table1

    ...if the AD HOC queries were permitted (I don't know exactly what this means).

    but, how can Add it to my linked servers.

    Can you help me to troublesshot it ?

    Thanks,

    CS

  • Someone please correct me if I am wrong, but after trying to do what you stated, I was told that the Access database has to be on to server in order to link to it.

    Russel Loski, MCSE Business Intelligence, Data Platform

  • Can you tell me what the error is you get when you run sp_addlinkedserver?  Your syntax looks correct.

    Paul

     

    - Paul

    http://paulpaivasql.blogspot.com/

  • The linked server is added ok, but the when I try to get the data the error is:

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

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyServer" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyServer" reported an error. Authentication failed.

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyServer".

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

    As I mentioned, the access .mdb is secured, so I've followed the recomendation posted at:

    http://msdn2.microsoft.com/en-us/library/aa238103(SQL.80).aspx

    http://msdn2.microsoft.com/en-us/library/aa226395(SQL.80).aspx

    but, I'm still having problems with this linked server.

    CS

  • Can you please show a sample of the calling code which generates that error.

    - Paul

    http://paulpaivasql.blogspot.com/

  • Select * from MyServer...table1

    GO

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

    The problem I think is in the "Microsoft.Jet.4.0.OLE DB Provider".

    When I test the connection using a .udl file it works fine, but I don't know how to tell SQLServer that have to use the same connection string.

    The string used by the .uld is:

    [oledb]

    ; Everything after this line is an OLE DB initstring

    Provider=MSDASQL.1;Persist Security Info=False;Data Source=testcasj

    "testcasj" is an ODBC created for the test which works ok.

    Q: How do I proper set up my linked server to use the "Microsoft OLE DB Provider for ODBC" instead of the "Microsoft.Jet.4.0.OLE DB Provider"

    CS

  • When you say the databases is secured, do you mean that it has a password?  If so, try this, where password is "yaya".  I believe the provider you are using (Jet) is already correct.

    EXEC

    sp_addlinkedserver

    @server

    = 'TestLinkAccess',

    @Provider

    = 'Microsoft.Jet.OLEDB.4.0',

    @SrvProduct

    = 'OLE DB Provider for Jet',

    @DataSrc

    = 'C:\Paul\db1.mdb',

    @ProvStr

    = ';pwd=yaya;'

     

    - Paul

    http://paulpaivasql.blogspot.com/

  • Thats ok, the db has a password.I've tested your script with the correct password, but the error is the following:

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

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TestLinkAccess" returned message "Not a valid account name or password.".

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TestLinkAccess" reported an error. Authentication failed.

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TestLinkAccess".

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

    Can you send me the db1.mdb database that you have used for your test ?

    Thanks a lot,

    CS

  • If it is the database password, you need a different element try:

    Database Password=MyDbPassword;

    rather than pwd=MyDbPassword

    Russel Loski, MCSE Business Intelligence, Data Platform

  • Here is an issue:

    Your script worked ok in SQL2000 but not in SQL2005. But I need it to work in my SQL2005 becuase I've to migrate from 2000.

    CS

  • Here's all my code.  I'm using SQL 2005, developer version.

    EXEC sp_addlinkedserver

    @server = 'TestLinkAccess',

    @Provider

    = 'Microsoft.Jet.OLEDB.4.0',

    @SrvProduct

    = 'OLE DB Provider for Jet',

    @DataSrc

    = 'C:\Paul\db1.mdb',

    @ProvStr

    = ';pwd=yaya;'

    SELECT

    *

    FROM

    TestLinkAccess...Table1

    - Paul

    http://paulpaivasql.blogspot.com/

  • Could it be the Jet driver ? or I'm with a stupid think !

    What about the registry entries for the Jet Engine ?

    The message regarding the "Workgroup information file" can be my key, but I don't know how to hadle it.

    CS

  • So did the db I sent work for you?  If not, then you have a tough situation.

    Next I would suggest re-applying sp1 of SQL 2005 - it has been known to be problematic on the first install and has cause lots of issues. 

    After that, I suggest modifying the environment (remove security, change file location to a different machine, change machine from which you call the code, change SQL 2005 machine) until it works, then add them back one-at-a-time to try and gain insight into what your particular environmental problem is.

    Good luck.

    Paul

    - Paul

    http://paulpaivasql.blogspot.com/

  • After several tries I've found the solution for the local access .mdb file:

    1.Use the provided script by Paul:

    EXEC sp_addlinkedserver

    @server = 'TestLinkAccess',

    @Provider = 'Microsoft.Jet.OLEDB.4.0',

    @SrvProduct = 'OLE DB Provider for Jet',

    @DataSrc = 'c:\casj.mdb',

    @ProvStr = ';pwd=test;'

    2.Configure a Workgroup Information file (MyWIF.mdw) with an user and password different than the Admin. Here is where MSAccess is needed.

    3.Change the registry key:

    HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\SystemDB to point MyWIF.mdw

    4. Use the sp_addlinkedsrvlogin with that user and password defined in the Workgroup Information file:

    exec sp_addlinkedsrvlogin

    TestLinkAccess ,

    false,

    null,

    'MyUser',

    'MyPwd'

    5. Test the of the data:

    Select * from TestLinkAccess...table1

    -----------

    But, if the file is in the network, the

    @DataSrc = 'c:\casj.mdb'

    must be changed to

    @DataSrc = '\\Server\Shared\casj.mdb',

    Then, I've this error:

    ----

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TestLinkAccess" returned message "Disk or network error.".

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TestLinkAccess".

    ----

    And again, this remote .mdb works fine in SQL2000 but not in SQL2005

    What can be done ?

    Thanks,

    CS

Viewing 14 posts - 1 through 13 (of 13 total)

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