Brad...
This is what I use; you should be able to piece together the parts that
interest you (essentially the ODBC logon line using 'strConnect '...)
'I use this procedure to re-create links to Oracle.
'There is a local Access table (tblODBCTables) that contains the table names
'and primary key fields I want to link to on the Server.
'Note: the source table name needs the Schema User prefix which is in the
'code. The linked table name usually omits this.
Public Function LinkOracleTables(pcx_ODBC_Tabl As String, pcx_DSN_Stri As
String, pcv_Sche_Stri As Variant, pcv_ID_Stri As Variant, pcv_Sche_Pass_Stri
As Variant) As Boolean
Dim db As DAO.Database, rs As DAO.Recordset, tdfAccess As TableDef, qdf As
QueryDef
Dim dbODBC As Database, strConnect As String, strSQL As String
On Error GoTo Err_LinkOracleTables
If pcx_DSN_Stri = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Function
Else
'here is the connection string to Oracle, taking passed parameters
(change p999 for your Server DSN ID...)...
strConnect = "ODBC;DSN=" & pcx_DSN_Stri & ";Server=p999;UID=" &
pcv_ID_Stri & ";PWD=" & pcv_Sche_Pass_Stri & ";"
End If
SysCmd acSysCmdSetStatus, "Connecting to Oracle..."
Call DeleteODBCTableNames
Set db = CurrentDb
Set rs = db.OpenRecordset(pcx_ODBC_Tabl)
Set dbODBC = OpenDatabase("", False, True, strConnect)
DoCmd.SetWarnings False
Do While Not rs.EOF
Set tdfAccess = db.CreateTableDef(pcv_Sche_Stri & "_" &
rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbODBC.Connect
tdfAccess.SourceTableName = pcv_ID_Stri & "." & rs![LinkTablename]
db.TableDefs.Append tdfAccess
'run pseudo index queries here. If the table does not exist then
this gets skipped.
If rs![IndexFields] <> "" Then
strSQL = "CREATE INDEX " & rs![LinkTablename] & "_INDEX ON " &
rs![LinkTablename] & " (" & rs![IndexFields] & ");"
DoCmd.RunSQL strSQL
End If
'If rs![IndexFields] <> "" Then
' db.Execute "CREATE INDEX " & rs![LinkTablename] & "_INDEX ON "
& rs![LinkTablename] & " (" & rs![IndexFields] & ");"
'End If
TableNotInCollection:
rs.MoveNext
Loop
LinkOracleTables = True
Exit_LinkOracleTables:
On Error Resume Next
DoCmd.SetWarnings True
rs.Close
Set rs = Nothing
Set dbODBC = Nothing
Set db = Nothing
SysCmd acSysCmdClearStatus
Exit Function
Err_LinkOracleTables:
Select Case Err.Number
Case 3151
MsgBox ("There is an ODBC datasource problem." & vbCrLf & "Please
verify the DSN and database are spelled correctly." & vbCrLf & "Note: They
can be case sensitive.")
Case 3011 'item not in collection - table does not exist, or can 't find
object
MsgBox (Err.Number & " : " & rs![LinkTablename] & " not available...")
Resume TableNotInCollection
Case 3265, 7874 'item not in collection - table does not exist, or can
't find object
MsgBox (Err.Number)
Resume TableNotInCollection
Case Else
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source &
vbCrLf & Err.Description, , "LogOnCode - LinkOracleTables"
End Select
LinkOracleTables = False
Resume Exit_LinkOracleTables
End Function
Hope this helps...
--
Have a nice day
Douglas
Brad said:
I want to use Access to pull data nightly from a remote SQL-Server database.
I have the ODBC connection working, but it requires that a password is
entered for each nightly run. The password cannot be removed.
Is there a way in VBA to make the ODBC connection and plug in the password
automatically so that no manual intervention is needed each night when this
process takes place?
Thanks in advance for your help.
Brad
