Here's some demo code to get you started. You'll need to get the specific values to assign to sConnect from the administrator of the SQL database.
Option Compare Database
Option Explicit
Sub ODBC_Demo() 'demonstrates how to read an ODBC data source from VBA code
Dim Wrk1 As Workspace 'workspace object variable
Dim Con1 As Connection 'connection object variable
Dim bReadOnly As Boolean 'variable indicating whether connection is to be read-only
Dim sConnect As String 'variable to hold ODBC connection string
Dim Rst1 As Recordset 'recordset object variable
Dim I As Integer 'loop counter variable
Dim sTemp As String 'temporary string variable
Set Wrk1 = CreateWorkspace("MyWorkspace", "MyWorkspacePassword", "", dbUseODBC) 'create ODBC workspace object
bReadOnly = True 'specify a read-only connection
sConnect = "ODBC;DATABASE=MyDatabase;UID=MyID;PWD=;DSN=MyDataSet" 'construct ODBC connection string
Set Con1 = Wrk1.OpenConnection("CN1", dbDriverNoPrompt, bReadOnly, sConnect) 'open ODBC connection
Set Rst1 = Con1.OpenRecordset("MyTable") 'open recordset to table MyTable
On Error GoTo Error_Handler 'enable error handling
With Rst1 'begin using recordset object
For I = 1 To 3 'loop to process first three records in table
sTemp = !sSSN & ", " & Trim(!sFirstName) & " " & Trim(!sLastName) 'build string to display
MsgBox (sTemp) 'display string
.Edit 'attempt to edit record - will cause an error since connection is read-only
.Update 'attempt to update record - will cause an error since .Edit failed
.MoveNext 'move to next record
Next I 'end of loop
End With 'stop using recordset object
On Error GoTo 0 'disable error handling
Set Rst1 = Nothing 'clear recordset object
Set Con1 = Nothing 'clear connection object
Set Wrk1 = Nothing 'clear workspace object
MsgBox ("ODBC Demo Completed.") 'confirm completion of routine
Exit Sub 'exit from Sub
Error_Handler: 'error handling routine
MsgBox (Err & " : " & Err.Description) 'display error number and description
Resume Next 'resume execution with next statement
End Sub
.
