Douglas,
Thanks a bunch, I really appreciate the example.
Brad
"Dad3353" wrote:
> 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" wrote:
>
> > 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
> >
> >
|
Similar Threads
