[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 982
  • Last Modified:

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)
   
    Myset.MoveFirst
    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
      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-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?

Thanks,
S C
0
saichak
Asked:
saichak
  • 5
  • 5
1 Solution
 
nico5038Commented:
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.

Nic;o)
0
 
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,
S C
0
 
saichakAuthor Commented:
Hi,

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

Thanks & Regards,
S C
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
nico5038Commented:
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.

Nic;o)
0
 
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??

FOR ACCESS QUERY
---------------------------------------
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
Qdf.Execute
Qdf.Close

FOR EQUIVALENT ORACLE VIEW
-------------------------------------------
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
Qdf.Execute
Qdf.Close



Thanks & Regards,
S C
0
 
nico5038Commented:
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)
0
 
saichakAuthor Commented:
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
0
 
nico5038Commented:
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:
http://www.experts-exchange.com/Databases/Oracle/Q_20996786.html

Nic;o)
0
 
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 .
0
 
nico5038Commented:
Glad I could help and success with the application !

Nic;o)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now