Connecting to Oracle

[asunpraise]

I need to import data from Oracle database to Access, and I'm already done with it. The connection variable is:

CON = "IN RPD[ODBC;SERVER=aserver;UID=DDDD;PWD=XXXXX]"

The problem is that I have to import more than one tables, and every time when I have sql statement such as:


'COPY BENEFITS_RECEIVED FROM ORACLE

strSQL = "SELECT * INTO BENEFITS_RECEIVED FROM ALL_RECEIVED " & CON & " " & _
"WHERE CLI_CLIENT_ID IN (SELECT CLI_CLIENT_ID FROM ALL_RECEIVED " & CON & " " & _
&quot;WHERE IDATE<#&quot; & E + 1 & &quot;#);&quot;
Set qdf = dbs.CreateQueryDef(&quot;MAKETABLE&quot;, strSQL)
DoCmd.OpenQuery &quot;MAKETABLE&quot;, acViewNormal
DoCmd.DeleteObject acQuery, &quot;MAKETABLE&quot;

data source window pops up, which means if I have 20 tables needing to be imported, I have to click on the data source name 20 times. How would I solve this problem? I wish there is a one time connection instead of putting &quot;CON&quot; in every single SQL statement.

Thanks a million.
Lea

 

[kewo99]

Lea,
This is a function that I use to import Oracle data to Access tables. It works without any user intervention. I have the Oracle tables linked to the Access mdb using ODBC.
Ken

Function MakeNewTablesAuto()
Dim db As Database, ws As Workspace, strConnect As String
strConnect = &quot;ODBC;Database=oracledatabasename;DSN=zzz;UID=ddd;PWD=xxxxx&quot;
Set ws = DBEngine(0)
On Error GoTo Err_odbc1
Set db = ws.OpenDatabase(&quot;&quot;, False, False, strConnect)
On Error GoTo 0
DoCmd.Hourglass True
Application.Echo False, &quot;Copying Oracle Tables to MS Access&quot;
DoCmd.SetWarnings False
DoCmd.RunSQL &quot;Delete * From TblDispositionData&quot;
DoCmd.OpenQuery &quot;tblDispositionData_Append&quot;
DoCmd.RunSQL &quot;Delete * From TblInspectionData&quot;
DoCmd.OpenQuery &quot;tblInspectionData_Append&quot;
DoCmd.RunSQL &quot;Delete * From TblInspSampleData01&quot;
DoCmd.OpenQuery &quot;tblInspSampleData01_Append&quot;
DoCmd.RunSQL &quot;Delete * From TblNonconformanceData&quot;
DoCmd.OpenQuery &quot;tblNonconformanceData_Append&quot;
DoCmd.RunSQL &quot;Delete * From TblRootCause&quot;
DoCmd.OpenQuery &quot;tblRootCause_Append&quot;
DoCmd.RunSQL &quot;Delete * From TblPartData&quot;
DoCmd.OpenQuery &quot;tblPartData_Append&quot;
DoCmd.RunSQL &quot;Delete * From tblReceiptData&quot;
DoCmd.OpenQuery &quot;tblReceiptData_Append&quot;
DoCmd.RunSQL &quot;Delete * From tblSupplierData&quot;
DoCmd.OpenQuery &quot;tblSupplierData_Append&quot;
DoCmd.RunSQL &quot;Delete * From TblInspSampleData&quot;
DoCmd.OpenQuery &quot;tblInspSampleData_Append&quot;
DoCmd.SetWarnings True
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset(&quot;tblSetup&quot;, dbOpenDynaset)
rs.Edit
rs!DataUpdateDate = Now()
On Error Resume Next
rs.Update
rs.Close
Exit_odbc1:
Set rs = Nothing
Set db = Nothing
Set ws = Nothing
DoCmd.Hourglass False
Application.Echo True
On Error GoTo 0
Exit Function
Err_odbc1:
Resume Exit_odbc1
End Function

 

[asunpraise]

Sorry, I tried, but i don't know why it's not working.

At least when this line gets excecuted,

Set db = ws.OpenDatabase(&quot;&quot;, False, False, strConnect)


Data source window pops up, right?

 

[kewo99]

I setup the data source first, then used that to link the Oracle tables to the Access mdb.
The line Set db = ws.OpenDatabase ... just automatically refreshes the ODBC connection so that no user response is needed.

Ken