ODBC Connection From Access to Oracle (Table problem)

[Kozia]

I have a code:
Dim wrkRemote As Workspace
Dim dbsRemote As Database
Dim strConnect As String
Dim rst As ADODB.Recordset
Dim db1 As Database
Dim rst2 As Recordset
Dim rname As String

strConnect = "ODBC;DSN=ORAPRD0;UID=******;DBQ=PRD0;ASY=OFF;PWD=****"

Set wrkRemote = DBEngine.Workspaces(0)
Set db1 = wrkRemote.OpenDatabase("", False, False, strConnect)
rname = "LAND_REP"
Set rst2 = db1.OpenRecordset(rname, dbOpenDynaset)

So the connection is open why can't I query tables? Do they need to be refreshed or something?
It complains that the table or query LAND_REP is not found. I am just trying to test the connection between oracle and Access. Because I have a whole Access app working except I need to put tables into Oracle. At the moment they are in mdb file but on Monday they will be moved into Oracle. Is it going to throw everything off? All my queries and interaction between forms and tables?

 

[borg2002]

Dim conn as ADODB.connection
Dim rstmat as ADODB.recordset
Dim strc as String

Set conn = New ADODB.Connection 'Define new ADO connection
Set rstmat = New ADODB.Recordset

strc = "dsn=DSNNAME"

With conn
.CursorLocation = adUseClient 'Define connection as being client side
.ConnectionString = strc 'Use parameters provided in strc String to make a DSN connection
.Open 'Open connection
End With

With rstmat
.CursorLocation = adUseServer 'Define recordset as being server side
.CursorType = adOpenKeyset 'Define cursortype
.LockType = adLockOptimistic 'Define locktype allowing updates
.CacheSize = 50
.Source = "Select * from TABLENAME"
.ActiveConnection = conn 'Define connection to be used for the recordset
.Open 'Open recordset
End With

The DSN I am using also connects using Oracle drivers. The above code does the trick for me.

 

[Kozia]

I am getting an error msg on .Open

error msg [ORACLE][ODBC][Ora]ORA-0093:missing expression

But if I just use straight access such as:
Sub CreateNewRec()
Dim dbs As Database
Dim rstSet As DAO.Recordset
Dim strSQL As String
Dim tmpstring As String, tmpSupervisor As String

Set dbs = CurrentDb()

tmpstring = Me![HoldCostId]
tmpSupervisor = Me![HoldSupervisorID]

strSQL = "SELECT [BUDLINK_DIRECTCOST].*"
strSQL = strSQL & " FROM [BUDLINK_DIRECTCOST]"
strSQL = strSQL & " WHERE ((([BUDLINK_DIRECTCOST].COSTID)='" & tmpstring & "') And (([BUDLINK_DIRECTCOST].[SUPERVISOR_CODE])='" & tmpSupervisor & "'));"


Set rstSet = dbs.OpenRecordset(strSQL)

If rstSet.RecordCount < 1 Then
rstSet.AddNew
rstSet![CostId] = Me![HoldCostId]
rstSet![Supervisor_Code] = Me![HoldSupervisorID]
rstSet.Update
End If


End Sub
It see the records but I can't add one it ends on rstSet.Update with msg &quot;Insert to a linked table failed&quot;

 

[borg2002]

Try this...
If rstSet.Recordcount < 1 then
rstset.addnew
rstset.fields(&quot;CostID&quot=me.HoldCostID
rstset.fields(&quot;Supervisor_Code&quot=me.holdsupervisorID
rstset.update
end if

You can also try to use rstset.fields(number).
Where number is the number of the field in your recordset. (Counting starts with 0)

Kind Regards
Borg