CODEWITHSUNDEEP
sqlsql-servervbams-access
Turtlemp
Turtlemp

Reputation: 33

Export a table from sql server into acccess table using VBA

My goal is to export a table from SQL Server to MS Access. This is my code so far and it runs but does nothing. Any help would be appreciated? I changed the servername in the code below but the rest is the same.

Option Compare Database
Option Explicit

Public Sub adotest()


    Dim ADOConn As Object
    Set ADOConn = CreateObject("ADODB.Connection")
    Dim db As Database
    Set db = CurrentDb()

    On Error Resume Next

    ADOConn.ConnectionString = "Driver={SQL Server};Server=SERVERNAME;Database=Model;Trusted_Connection=Yes;"
    ADOConn.Open '"Driver={SQL Server};Server="servername";Database=Model;Trusted_Connection=Yes;"

   Debug.Print ADOConn.State


    ADOConn.Execute "insert into models (model, bookid) select model, bookid from [odbc;Driver={SQL Server};Server="servername";Database=Model]"

Upvotes: 0

Views: 137

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49319

Well, you can't use ADO between two different databases because each ado object only allows one connection. On the other hand Access most certainly works with multiple connections at the same time. You could perhaps create two ADO recordsets, and loop row by row (and of course column by column). But, that's way too much work.

Assuming you have a correct connection, such as a linked table to the sql server. (and for testing, good idea to "test" link a table to that sql server.

So, you can use this code:

Dim strCon        As String
strCon = CurrentDb.QueryDefs("qrypassR").Connect

DoCmd.TransferDatabase acLink, "ODBC Database", strCon, acTable, _
   "tblHotelsServerSide", _
   "tblHotelsClientSide", False, True

Debug.Print "done"

the above would create a brand new link to the sql server (not transfer the table).

However if you want to transfer the data, and not create a link? Then replace in above the "acLink" to acImport.

So, you just need a valid connection string for the above (ODBC). So, if you have any existing linked table, then grab/steal that known working connection in code.

eg:

 strCon = CurrentDb.TableDefs("Some linked table").Connect

You of course could also just assign a string as a connection in code as opposed to "lifting" an existing connection (if you don't have one). And, creating a linked table is a great way to ensure that your connection string is correct - so access can build + setup a linked table for you, and then in your code use that existing working connection as per above code example.

So, it rather difficult to do this with a ADO (or DAO object), since such objects tend to lack support for two different active connections at the same time, and you need this ability for a easy transfer of data, or you be hand coding loops etc., and dealing with two ADO objects with two different connections, and there is little methods or features that allows transfer of data between two ADO objects - so using the above access features is not only a great way to attack this problem, but it also a heck of a lot less code to boot.

Upvotes: 2

Related Questions