user2119980 user2119980 - 13 days ago 5
SQL Question

Using ADO/DAO Connection to Download data from SQL Server

I am trying to figure out how to download using an ADO/DAO connection in Access VBA to get the contents of a table from SQL server. I am trying to avoid using a linked table because the DB requires a password and I keep running into issues with getting it to not ask for the login info. Are there any ideas or references for me to start with on this matter?

Answer

It appears either way you'll need to provide SQL credentials.

There's more involved without linking a table, basically you'd want a recordset for the source and the "target" table to iterate over.

targetrs = CurrentDb.OpenRecordset("Target", dbOpenTable)

Dim Con As New ADODB.Connection
Dim sqlStr As String 
    Con.Open _
        "Provider = sqloledb;" & _
        "Data Source=SqlServer;" & _
        "Initial Catalog=MyDB;" & _
        "User ID=sa;" & _
        "Password=p@ssW0rd;"

Dim rsSource As New ADODB.Recordset 
    rsSource.Open "select * from SOURCE", Con

    do until rsSource.eof
        targetrs.addnew
        for each field in rsSource
            targetrs.fields(field.Name) = rsSource.fields(field.Name)
        next
        targetrs.update
        rssource.movenext
    loop

Since you still have to have the credentials, you could dynamically link the table instead:

docmd.TransferDatabase acLink,"ODBC Database",
"ODBC;Driver={SQL Server};Server=MySQLServer;Database=MYSQLDB;
Uid=USER;Pwd=PASSWORD",acTable,"SQLtable","MyAccessTable"