Dear EE members,
I would like to call an access macro. This macro will call the vba function with the following code
to insert record from MS Server to Access table, but I failed to make it. Any suggestion to resolve
this problem are welcome.
Code:
Function fnADO_test() As Boolean
Dim adoCon As ADODB.Connection
Dim sConnect As String
Dim theQuery As String
Set adoCon = New ADODB.Connection
sConnect = "Provider='sqloledb';Data Source=myDataSource;Initia
l Catalog=myDB;User ID =myUser;Password=myPasswor
d;"
Debug.Print sConnect
adoCon.ConnectionString = sConnect
adoCon.Open
theQuery = _
"INSERT INTO sTarget" & _
" SELECT col2,col4,col6
" FROM dbo_Prod " & _
" WHERE col2 < col4 & _
" ORDER BY col1;"
Debug.Print theQuery
MsgBox "Before Execute"
adoCon.Execute theQuery, , adCmdText Or adExecuteNoRecords
MsgBox "After Execute"
Set adoCon = Nothing
End Function
Start Free Trial
