Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
1  
Any reason why you cannot link the SQL Server table? – Remou Sep 26 '12 at 12:46
@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 Sep 26 '12 at 12:55

2 Answers

up vote 1 down vote

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

share|improve this answer
I do not have available, nor want to use an ODBC connection. – Danny Beckett Sep 26 '12 at 12:54
Linked tables use ODBC. – Remou Sep 26 '12 at 12:56
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/… ) – Remou Sep 26 '12 at 13:09
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. – Danny Beckett Sep 26 '12 at 13:49
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 – Remou Sep 26 '12 at 13:54
show 7 more comments
up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.