saichak
asked on
Runtime Error '3001'
Dear Experts,
I have a severe problem with my Access 2000 application and database. The back-end has been migrated to Oracle 9i using Oracle Migration Workbench Tool.What I understand is I can have access to oracle tables in one of two ways.
1.Connecting through access queries that are created during Migration with the same name as Old Access tables.
2.Connect directly to Oracle with ODBC Dsn and access them directly.
As the first option need a lot of code change like replacing DBOPENTABLE option with DBOPENDYNASET and so many, I choose the second option.
As there are some temporary table in Access itself , I need to be connected to the current Access tables and queries as well.
Please find below the code what I have used to connect to databases.
**************************
Function setup(Typ)
On Error GoTo Error_setup
FirstTimeIn = True
WhichTable = "1"
Set Wspace = DBEngine.Workspaces(0)
Set Thisdb = CurrentDb()
Set Myset = CurrentDb.OpenRecordset("T
Myset.MoveFirst
Datdir = Myset!Datdir
GlDataStr = Myset!Datdir & Myset!Datfile
' Set Datadb = Wspace.OpenDatabase(GlData
Set Myset = Thisdb.OpenRecordset("VERS
Myset.Index = "PrimaryKey"
If Not (Myset.BOF And Myset.EOF) Then
Myset.MoveLast
GlVersion = Myset!VER_NO
End If
Dim dsn As String
Dim uid As String
Dim pwd As String
Dim odbcConnectStr As String
dsn = "rnporacle"
uid = "rnpdata"
pwd = "oracle"
' build up the connect string
odbcConnectStr = "ODBC;DSN=" & dsn & ";UID=" & uid & ";PWD=" & pwd
' use Microsoft JET Workspace to make a connection to the Oracle database
Set Datadb = Wspace.OpenDatabase(dsn, dbDriverCompleteRequired, False, odbcConnectStr)
Resume_setup:
On Error Resume Next
Myset.Close
If WhichTable = "99" Then
DoCmd.OpenForm "FRM-Main Menu"
' DoCmd.OpenForm "FRM-Reattach Tables"
ElseIf Typ = "CO" Then
DoCmd.OpenForm "FRM-Call Offs" ', , , , , acDialog
Else
DoCmd.OpenForm "FRM-Main Menu"
End If
DoCmd.Maximize
Myset.Close
Set Myset = Nothing
Error_setup:
WhichTable = "99"
Resume Resume_setup
End Function
**************************
It gets connected to both database as this code part passes through smoothly but problem lies when I am going to open Oracle table with following code:-
**************************
Set User = Datadb.OpenRecordset("TBL-
**************************
The code does not allow to open the table . Error given is "Runtime Error '3001': Invalid Arguement".
Could you please help me get out of the problem by giving valuable tips?
Thanks,
S C
I have a severe problem with my Access 2000 application and database. The back-end has been migrated to Oracle 9i using Oracle Migration Workbench Tool.What I understand is I can have access to oracle tables in one of two ways.
1.Connecting through access queries that are created during Migration with the same name as Old Access tables.
2.Connect directly to Oracle with ODBC Dsn and access them directly.
As the first option need a lot of code change like replacing DBOPENTABLE option with DBOPENDYNASET and so many, I choose the second option.
As there are some temporary table in Access itself , I need to be connected to the current Access tables and queries as well.
Please find below the code what I have used to connect to databases.
**************************
Function setup(Typ)
On Error GoTo Error_setup
FirstTimeIn = True
WhichTable = "1"
Set Wspace = DBEngine.Workspaces(0)
Set Thisdb = CurrentDb()
Set Myset = CurrentDb.OpenRecordset("T
Myset.MoveFirst
Datdir = Myset!Datdir
GlDataStr = Myset!Datdir & Myset!Datfile
' Set Datadb = Wspace.OpenDatabase(GlData
Set Myset = Thisdb.OpenRecordset("VERS
Myset.Index = "PrimaryKey"
If Not (Myset.BOF And Myset.EOF) Then
Myset.MoveLast
GlVersion = Myset!VER_NO
End If
Dim dsn As String
Dim uid As String
Dim pwd As String
Dim odbcConnectStr As String
dsn = "rnporacle"
uid = "rnpdata"
pwd = "oracle"
' build up the connect string
odbcConnectStr = "ODBC;DSN=" & dsn & ";UID=" & uid & ";PWD=" & pwd
' use Microsoft JET Workspace to make a connection to the Oracle database
Set Datadb = Wspace.OpenDatabase(dsn, dbDriverCompleteRequired, False, odbcConnectStr)
Resume_setup:
On Error Resume Next
Myset.Close
If WhichTable = "99" Then
DoCmd.OpenForm "FRM-Main Menu"
' DoCmd.OpenForm "FRM-Reattach Tables"
ElseIf Typ = "CO" Then
DoCmd.OpenForm "FRM-Call Offs" ', , , , , acDialog
Else
DoCmd.OpenForm "FRM-Main Menu"
End If
DoCmd.Maximize
Myset.Close
Set Myset = Nothing
Error_setup:
WhichTable = "99"
Resume Resume_setup
End Function
**************************
It gets connected to both database as this code part passes through smoothly but problem lies when I am going to open Oracle table with following code:-
**************************
Set User = Datadb.OpenRecordset("TBL-
**************************
The code does not allow to open the table . Error given is "Runtime Error '3001': Invalid Arguement".
Could you please help me get out of the problem by giving valuable tips?
Thanks,
S C
ASKER
Dear expert,
My tables are automatically converted to link tables.
Suppose I had a access table TBL-MyTable. after Migration a table (linked ODBC) has been created for this table with the name TBL-MyTable_R. Apart from that a query also has been created as TBL-Mytable(the same name as the table in original Access version). Now if I keep the same name in the code, then this is not a table actually. It is a query now. So,I need to change the option from dbopentable to dbopendynaset. again there are many more code lines that are not being supported by a recordset opened with DbOpenDynaset option . That will cause a huge code change through 75-80 forms used in the application. So, to keep the code intact I used Oracle connection. Here I need to change the names of the table with an additional "_R".
Any suggestion if I want to keep connection with Oracle directly(as in my posted question)
Thanks & Regards,
S C
My tables are automatically converted to link tables.
Suppose I had a access table TBL-MyTable. after Migration a table (linked ODBC) has been created for this table with the name TBL-MyTable_R. Apart from that a query also has been created as TBL-Mytable(the same name as the table in original Access version). Now if I keep the same name in the code, then this is not a table actually. It is a query now. So,I need to change the option from dbopentable to dbopendynaset. again there are many more code lines that are not being supported by a recordset opened with DbOpenDynaset option . That will cause a huge code change through 75-80 forms used in the application. So, to keep the code intact I used Oracle connection. Here I need to change the names of the table with an additional "_R".
Any suggestion if I want to keep connection with Oracle directly(as in my posted question)
Thanks & Regards,
S C
ASKER
Hi,
Is there anything wrong in the code I have written??
Thanks & Regards,
S C
Is there anything wrong in the code I have written??
Thanks & Regards,
S C
In your code sample the Datadb is commented out:
' Set Datadb = Wspace.OpenDatabase(GlData
Set Myset = Thisdb.OpenRecordset("VERS
and you use "Thisdb" for the other "Set" statements.
Personally I just use linked tables via ODBC and have not trouble to do so for Oracle, MS SQL, Pervasive, .csv-files, etc.
Thus I'm more or less puzzled by the "query as table" trick you use.
Nic;o)
' Set Datadb = Wspace.OpenDatabase(GlData
Set Myset = Thisdb.OpenRecordset("VERS
and you use "Thisdb" for the other "Set" statements.
Personally I just use linked tables via ODBC and have not trouble to do so for Oracle, MS SQL, Pervasive, .csv-files, etc.
Thus I'm more or less puzzled by the "query as table" trick you use.
Nic;o)
ASKER
Many Thanks. I have followed your path.
Another new issue !! The parametered queries in Access are being transformed to Oracle views. I need to know how to write code to execute those views from Access VBA code.
I have some idea of code that should be run. I'm providing both type of code, i.e. to execute access query as well as Oracle view. Could you please confirm me whether I'm going in write direction??
FOR ACCESS QUERY
--------------------------
Dim Qdf As QueryDef
Set Qdf = Thisdb.QueryDefs("QRY-UP-T
Qdf.Parameters("AddThis") = MoveBy
Qdf.Parameters("FromThis")
Qdf.Parameters("DayNo") = Day
Qdf.Execute
Qdf.Close
FOR EQUIVALENT ORACLE VIEW
--------------------------
Set Qdf = Thisdb.QueryDefs("VIEW_NAM
With Qdf
.sql = "omwb_emulation.utilities.
.Type = "dbSQLPassThrough with Stored Proc."
End With
Qdf.Parameters("VIEW_NAME:
Qdf.Parameters("VIEW_NAME:
Qdf.Parameters("VIEW_NAME:
Qdf.Execute
Qdf.Close
Thanks & Regards,
S C
Another new issue !! The parametered queries in Access are being transformed to Oracle views. I need to know how to write code to execute those views from Access VBA code.
I have some idea of code that should be run. I'm providing both type of code, i.e. to execute access query as well as Oracle view. Could you please confirm me whether I'm going in write direction??
FOR ACCESS QUERY
--------------------------
Dim Qdf As QueryDef
Set Qdf = Thisdb.QueryDefs("QRY-UP-T
Qdf.Parameters("AddThis") = MoveBy
Qdf.Parameters("FromThis")
Qdf.Parameters("DayNo") = Day
Qdf.Execute
Qdf.Close
FOR EQUIVALENT ORACLE VIEW
--------------------------
Set Qdf = Thisdb.QueryDefs("VIEW_NAM
With Qdf
.sql = "omwb_emulation.utilities.
.Type = "dbSQLPassThrough with Stored Proc."
End With
Qdf.Parameters("VIEW_NAME:
Qdf.Parameters("VIEW_NAME:
Qdf.Parameters("VIEW_NAME:
Qdf.Execute
Qdf.Close
Thanks & Regards,
S C
The "easy way" for this is to create a so-called "Pass through" query in Oracle SQL syntax.
Just create a new query without selecting a table and select "Query/Query Specific/PassThrough"
Now you can place the needed view there in Oracle syntax and for parameters just edit the query dynamically like:
Dim qd as DAO.QueryDef
set qd = currentdb.querydefs("name of passthrough query")
qd.SQL = "view xyz where AddThis= " & Moveby & " and FromThis=" & From & " and ....
After the assignment you can use the query.
Getting te idea ?
Nic;o)
Just create a new query without selecting a table and select "Query/Query Specific/PassThrough"
Now you can place the needed view there in Oracle syntax and for parameters just edit the query dynamically like:
Dim qd as DAO.QueryDef
set qd = currentdb.querydefs("name of passthrough query")
qd.SQL = "view xyz where AddThis= " & Moveby & " and FromThis=" & From & " and ....
After the assignment you can use the query.
Getting te idea ?
Nic;o)
ASKER
Hi NIC,
Thanks for your excellent idea but my problem is I can't follow it.
Actually The Views have been created already using Oracle Migration Workbench from Access queries(This is a part of Team Leader's decision, so I can't change it). I have to work with those oracle views instead of access queries.
So, could you please help me to do this?
Regards,
SC
Thanks for your excellent idea but my problem is I can't follow it.
Actually The Views have been created already using Oracle Migration Workbench from Access queries(This is a part of Team Leader's decision, so I can't change it). I have to work with those oracle views instead of access queries.
So, could you please help me to do this?
Regards,
SC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the link.ANyway I should close this question now as the original question has been answered long ago and the topic is now diverted to a separate question.
Thanks again .
Thanks again .
Glad I could help and success with the application !
Nic;o)
Nic;o)
When you first use ODBC to create a definition for your database, then you can simply link an Access table to this database and select the needed table(s). Just use the ODBC application (E.g. from the Settings panel)
Thus all queries, etc. will stay the same.
When you need to perform "heavy" queries you can consider to use so-called "Pass through" queries, but I would start with the way it is.
Nic;o)