Multiple databases in MS Access VBA?

Question

This Microsoft KB article details how to run a query on another database than the current one used by the Access project. However it only states how to connect to DBase, Foxpro, Paradox, BTrieve and ODBC.

I want to be able to do something like this:

UPDATE MSSQLDatabase.Table
    SET MSSQLDatabase.Table.Column = AccessDatabase.Table.Column
    WHERE MSSQLDatabase.Table.Column = AccessDatabase.Table.ID

INSERT INTO AccessDatabase.Table
    VALUES (AccessDatabase.Table.ID)

Can you give me any pointers of where to begin? The database I want to connect to is a SQL Server 2008 Provider Native connection. I'm using Access 2007.

To do this in VBA would be perfect.

@Remou I guess not, hadn't heard of linked tables before - can you elaborate a little? (Maybe you could modify your answer) Thanks! — Danny Beckett
– Danny Beckett, Commented Sep 26, 2012 at 12:55

Fionnuala · Accepted Answer · 2012-09-26 14:02:01Z

1

By far the easiest way to work with SQL Server in MS Access is to use linked tables. However, you can also run pass-through queries and refer to a connection in-line:

SELECT * FROM [ODBC;FILEDSN=Z:\Docs\Test.dsn;].table_1

Or

SELECT * FROM 
  [ODBC;DRIVER=SQL Server;SERVER=srvr;Trusted_Connection=Yes;DATABASE=Test;].table_1

Or

SELECT * FROM [ODBC;Driver={SQL Server Native Client 11.0};Server=svr;Database=test;Trusted_Connection=yes;].table_1

see also http://www.connectionstrings.com/sql-server-2008

edited Sep 26, 2012 at 14:02

answered Sep 26, 2012 at 12:53

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Danny Beckett Over a year ago

I do not have available, nor want to use an ODBC connection.

Fionnuala Over a year ago

I should add that ODBC is the recommended way to work with SQL Server. OleDB will soon be unsupported ( blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/… )

Danny Beckett Over a year ago

I do not have the facility to use an ODBC connection. It is on a customer's server, which I do not have such access to. I don't want to use OleDB either - I want to use Provider Native.

Fionnuala Over a year ago

Do you mean SQL Server Native Client? "All appropriate registry settings for the SQL Server Native Client OLE DB provider and the SQL Server Native Client ODBC driver are made as part of the installation process." -- msdn.microsoft.com/en-us/library/ms131321.aspx

Danny Beckett Over a year ago

I believe that is to be able to create an ODBC connection to SQL Native. I want to directly connect with SQL Native. I may be able to just use the connection string directly in there though. I'll try.

|

Danny Beckett · Accepted Answer · 2012-10-01 14:02:06Z

0

This solution allows to catch errors:

Private Sub Command10_Click()
    On Error GoTo Err1:
        Dim cn As ADODB.Connection
        Set cn = New ADODB.Connection
        With cn
            .Provider = "SQL Native Client"
            .ConnectionString = "Server=myserver\myinstance;Database=mydb;Uid=myuser;Pwd=mypass;]"
            .Open
        End With

        MsgBox "Connection successful!"
        cn.Close
        Exit Sub

    Err1:
        MsgBox Err.DESCRIPTION
End Sub

The only thing to note, is that within the Visual Basic Editor, you must first go to Tools > References, and check Microsoft ActiveX Data Objects 2.x Library.

answered Oct 1, 2012 at 14:02

Danny Beckett

21k27 gold badges114 silver badges144 bronze badges

Collectives™ on Stack Overflow

Multiple databases in MS Access VBA?

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related