Access VBA to update Access table from SQL server table source (1 Viewer)

[najorth]

I would like to consult my problem... I hope you could help me. I have created a code below to test whether I can run a query and retrieve a data from an SQL server table. And so far I can return the result using a messagebox. but somehow I just don't know how to use this connection to update the table inside this access file. Basically I want to use this as a front end file. then when the form is open it will automatically update the table inside this access file and load the data to the combo box as a list. I tried searching it here and read many discussions here and in google but currently I can't find the right solution. I really hope you could help me. Thank you!

Option Compare Database

Sub LocalServerConn_Test()


Set conn = New adodb.Connection
Set rst = New adodb.Recordset


strDBName = "DataSet"

strConnectString = "Provider = SQLOLEDB.1; Integrated Security = SSPI; " & _
    "Initial Catalog = " & strDBName & "; Persist Security Info = True; " & _
        "Worksation ID = abc123;"

            conn.ConnectionString = strConnectString

                conn.Open


strSQL = "SELECT DISTINCT dbo.abc.abc123 FROM dbo.abc"


rst.Open Source:=strSQL, ActiveConnection:=strConnectString, _
    CursorType:=adOpenDynamic, LockType:=adLockOptimistic

If rst.RecordCount = 0 Then
    MsgBox "No records returned"

    Else

        rst.MoveFirst
            Do While Not rst.EOF
                MsgBox rst.Fields("abc123").Value
                    rst.MoveNext
            Loop

End If

conn.Close
rst.Close

End Sub

 

[billmeye]

For clarification, what are you looking to do?

1. Add records to SQL table
2. Add records to Local Access table
3. Append records to SQL table
4. Append records to Local Access table
5. Create a new Local Access table based on your recordset rst

I may be able to help you once I know better what you are trying to accomplish. Thanks.

 

[najorth]

Hello Billmeye thank you for your help and clarification.

basically I would like to update a table in the Access file from an SQL Server. But I need to run a query first from SQL Server.

Lets say I have a huge data table in SQL Server. I need to run a query "SELECT DISTINCT Location FROM SQLServerName" from access vba, then also a table from the same Access file will be updated by using the result of the query from the SQL Server.

I wish you understand and can be a big help in helping me find a solution

 

[pr2-eugin]

So try Inserting into the table?

[COLOR=Blue][B]Dim dbObj As DAO.Database
Set dbObj = CurrentDB()[/B][/COLOR]

strDBName = "DataSet"

strConnectString = "Provider = SQLOLEDB.1; Integrated Security = SSPI; " & _
                   "Initial Catalog = " & strDBName & "; Persist Security Info = True; " & _
                   "Worksation ID = abc123;"
conn.ConnectionString = strConnectString
conn.Open

strSQL = "SELECT DISTINCT dbo.abc.abc123 FROM dbo.abc"

rst.Open Source:=strSQL, ActiveConnection:=strConnectString, _
                CursorType:=adOpenDynamic, LockType:=adLockOptimistic

If rst.RecordCount = 0 Then
    MsgBox "No records returned"
Else
    rst.MoveFirst
    Do While Not rst.EOF
        [COLOR=Blue][B]dbObj.Execute("INSERT INTO tableName (fieldA) VALUES (" & rst.Fields("abc123") & ")") [/B][/COLOR]
        rst.MoveNext
    Loop
End If
conn.Close
rst.Close
[COLOR=Blue][B]Set dbObj = Nothing[/B][/COLOR]

 

[najorth]

Hi pr2-eugin thank you for your reply. I think it's almost there . I'm still having a run-time error # 3061 "Too few parameters. Expected 2.". You might know the solution