Connecting to ODBC (oracle) with VBA

  • Thread starter Thread starter Christian
  • Start date Start date
C

Christian

Hello. I am attempting to automatically run a make-table
query that has a linked table as its source. I am using
the Docmd.OpenQuery "query" macro. The problem is that I
cannot send the connection info correctly via VBA (I tried
DAO and ADO). Instead, I am prompted for the user
id, ... This eventually will be run as a scheduled task.
Any ideas on how to solve this. Thanks.

Christian
 
Create SQL Pass-Through query (thus supplying logon id,
password, etc.) and create simple ADO code, something
like:

Sub PeriodicalUpdate()
Dim cnn As New ADODB.Connection
Dim strSQL As String

Set cnn = CurrentProject.Connection

StrSQL = " <enter your SQL string to make-table>"

cnn.Execute StrSQL
cnn.Close

Set cnn = Nothing

End Sub

To schedule it use form OnTimer event or Scheduled Task
(Windows) or check www.splinterware.com and download free
(or purchase) Windows Scheduler.

Hopefully this helps.

Lucky
 
Back
Top