DSN less SQL Connection to MS Access

I had a situation to create a FORM to import or link table to access from SQL Server. The SQL SERVER table name is referencedata. I had a solution of creating DSN name and create ODBC Connection in each system who uses the form.
DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;Driver={SQL Server};Server=192.168.222.22;UID=yourusername;PWD=yourpassword;LANGUAGE=us_english;" _
& "DATABASE=yourdatabasename", acTable, "referencedata", "sql_referencedata"
referencedata is table in MS SQL-SERVER (Source)
sql_referencedata is table to be created in MS Access table (Destination)

This process does not need any DSN names created through ODBC.

No comments:

Post a Comment