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("TMP-Temp Params", DB_OPEN_TABLE)
    Datdir = Myset!Datdir

    GlDataStr = Myset!Datdir & Myset!Datfile
    ' Set Datadb = Wspace.OpenDatabase(GlDataStr)
    Set Myset = Thisdb.OpenRecordset("VERSION", DB_OPEN_TABLE, dbReadOnly)
    Myset.Index = "PrimaryKey"
    If Not (Myset.BOF And Myset.EOF) Then
      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)

    On Error Resume Next
    If WhichTable = "99" Then
        DoCmd.OpenForm "FRM-Main Menu"
       ' DoCmd.OpenForm "FRM-Reattach Tables"
    ElseIf Typ = "CO" Then
        DoCmd.OpenForm "FRM-Call Offs" ', , , , , acDialog
        DoCmd.OpenForm "FRM-Main Menu"
    End If
    Set Myset = Nothing

    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-Secur_R", DB_OPEN_TABLE)

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?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hmm, why this much effort ?

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.

saichakAuthor Commented:
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,
saichakAuthor Commented:

Is there anything wrong in the code I have written??

Thanks & Regards,
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

In your code sample the Datadb is commented out:

    ' Set Datadb = Wspace.OpenDatabase(GlDataStr)
    Set Myset = Thisdb.OpenRecordset("VERSION", DB_OPEN_TABLE, dbReadOnly)

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.

saichakAuthor Commented:
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??

Dim Qdf As QueryDef
Set Qdf = Thisdb.QueryDefs("QRY-UP-TMP_JOBLIN_LINENO")
Qdf.Parameters("AddThis") = MoveBy
Qdf.Parameters("FromThis") = From
Qdf.Parameters("DayNo") = Day

Set Qdf = Thisdb.QueryDefs("VIEW_NAME")
With Qdf
     .sql = "omwb_emulation.utilities.setvarchar2"
     .Type = "dbSQLPassThrough with Stored Proc."
End With
Qdf.Parameters("VIEW_NAME:AddThis") = Moveby
Qdf.Parameters("VIEW_NAME:FromThis") = From
Qdf.Parameters("VIEW_NAME:DayNo") = Day

Thanks & Regards,
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 ?

saichakAuthor Commented:

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?

Just create a passthrough query (your Oracle sample is also indicating Passthrough) and place the needed SQL as if you're executing the view "inside" Oracle with passing the parameters.
Not sure how the Oracle syntax is, but perhaps the answer to this question gives an idea:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
saichakAuthor Commented:
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 .
Glad I could help and success with the application !

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.