Solved

OpenRecordset problem

Posted on 2004-10-20
31
7,971 Views
Last Modified: 2008-01-09
I have created a linked table in my Access 2000 database that links to the desired table of a back end SQL server database.
I run my Macros (VBA) and I got following Run-time error:

Run-time error '3622'
You must use the dbSeeChanges option with OpenRecordset when accessing a SQL server table that has an Identity column.

Here is my piece of code:

    Dim db As Object
    Dim rec As Object
    Dim TableToOpen As String
    Set db = CurrentDb()
    TableToOpen = DetermineRecordToOpen(strFileWithPath)
    Set rec = db.OpenRecordset(TableToOpen, , dbSeeChanges)

Also I got following error message when I use any of a type option in OpenRecordset:
    Set rec = db.OpenRecordset(TableToOpen, dbOpenDynaset, dbSeeChanges)

Run-time error '3001'
Invalid argument.

I have checked the string TableToOpen in my code, it has the name of the linked table.

Could you tell me what is wrong here and how to fix them? Thanks.


Xiao



0
Comment
Question by:yc_yc
  • 12
  • 10
  • 4
  • +2
31 Comments
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 350 total points
ID: 12364986
Do you have a reference to DAO set? If you don't, it will not know what dbSeeChanges evaluates to. Does this work:
Set rec = db.OpenRecordset(TableToOpen, , 512)
0
 
LVL 34

Expert Comment

by:flavo
ID: 12365078
Do we need to use ADO with MS SQL?
0
 
LVL 16

Assisted Solution

by:GreymanMSC
GreymanMSC earned 100 total points
ID: 12365081
If TableToOpen is valid, everything else looks alright.

It appears that you have a reference set to DAO, since dbSeeChanges is a declared constant.
Try early binding your objects and recompiling.

    Dim db As DAO.Database
    Dim rec As DAO.Recordset
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12365108
Trouble with constants is that if you don't have a reference set, and you don't have Option Explicit, the constant will be treated as an undeclared Variant and will evaluate to Null.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12365193
Why not try using ADO to instantiate recordset?

Private Sub Command0_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim sql As String
sql = "select * from " & TableToOpen
rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

With rs
  While Not .EOF
    Debug.Print .Fields(0).Value
    .MoveNext
  Wend
End With
End Sub


You may also be having a problem with DAO needing the tablename to be prefixed with the sql objects OWNER prefix, usually DBO. but could be anything like Xiao.TableName, DBO is assumed, so I expect that maybe ownership is not DataBase Owner(DBO)

Alan
0
 

Author Comment

by:yc_yc
ID: 12365829
When the variables are declared as:
Dim db As DAO.Database
Dim rec As DAO.Recordset
or
Dim db As ADO.Database
Dim rec As ADO.Recordset
or
Dim db As ADODB.Database
Dim rec As ADODB.Recordset

I got compiling error:

Compile error:
    User-defined type not defined.

Does this mean something and how do I know which reference set should be used and how to set it?

By the way, the Macors work well when the table is in local, not a linked table to the backend SQL server.


Thanks
Xiao
0
 
LVL 34

Assisted Solution

by:flavo
flavo earned 50 total points
ID: 12366196
In VBA editor go Tools - Refrecnes and Tick Microsoft DAO Obj. Library 3.?
0
 
LVL 16

Expert Comment

by:GreymanMSC
ID: 12366686
Comment from yc_yc :
     Does this mean something and how do I know which reference set should be used and how to set it?
----
Knowing which reference you need to use is easy in this case.  You want the one you need to use. :)  

Seriously, in this case since you are using Data Access Objects, so you need the Microsoft DAO Object Library.  If you want to switch to ADODB, then you'll need the Microsoft Active Data Objects library, and so forth.   Falvo has indicated where to find refences.  Go for the highest version you can see.  
 
For now you could just go with shanesuebsahakarn's solution:  Set rec = db.OpenRecordset(TableToOpen, , 512)
 
This will work in the short run, but learning how to set references now will help in the future.  It's also easer to program with the references set, though, because the editor will complete any constant, property, or method names from the library, and indicate which parameters are needed.  

It also helps not to have to remember magic numbers like 512. ;)
0
 

Author Comment

by:yc_yc
ID: 12376058
Yes, the problem above is from no reference set.
Now I got another run-time error after continuing run the Macro:

Run-time error '3251':
   Operation is not supported for this type of object.

Below is the piece of code:

    Dim myrec As DAO.Recordset
    Dim copyline,  panelID
    Dim myCount As Integer

    panelID = Value                'Value is a string type from passing parameter of the calling function
    If InStr(copyline, "Panel") > 0 Then
        Set myrec = db.OpenRecordset(TableToOpen, dbOpenDynaset, dbSeeChanges)
        myrec.Index = "PrimaryKey"        <- This is where the error occurs
        myrec.Seek "=", Value
        Do While myrec.NoMatch = False
            myCount = myCount + 1
            Value = panelID & " " & "(" & myCount & ")"
            myrec.Seek "=", Value
        Loop
        myrec.Close
    End If

Thanks for all your help.
 
0
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 350 total points
ID: 12376123
When using Seek, or setting the Index property, the recordset must be opened as a table-type recordset (i.e. dbOpenTable as the second argument).
0
 

Author Comment

by:yc_yc
ID: 12376937
But changing type 'dbOpenDynaset' to 'dbOpenTable in following statement
   Set myrec = db.OpenRecordset(TableToOpen, dbOpenTable, dbSeeChanges)

I got a new run-time error:

Run-time error '3219':
   Invalid operation.

Please note the TableToOpen is the linked tables to backend SQL server via ODBC.
If the type has to be 'dbOpenDynaset', how to solve the problem?

Thanks.


0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12378077
I'm not sure what your code is trying to achieve, but by the looks of it, it could be accomplished much more effectively using an SQL statement rather than searching through the recordset.
0
 
LVL 34

Expert Comment

by:flavo
ID: 12378090
agreed
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12378140
Hi Xiao,

why dont you try to explain to us what you are trying to actually do in your code?

Happy to help :)

Really need to know your objective here, not the code examples for now, just tell us step by step what you want to do.
These guys can make access stand on it's head and jump through hoops blind-folded, specially that Shane dude, but they are having a little difficulty knowing what you really want to achieve here.

Understanding your objective is the hard part, the code is usually easy after that.


Alan


0
 

Author Comment

by:yc_yc
ID: 12382631
We had set up a MS Access database, using the Macro to import our company panel testing data everyday.
My manager is planning to upsize this Access database to a SQL server. I made the tables linked to backend
SQL Server in my local Access database and tested the Macro, which is not working now.

The panel ID is unique, but each panel may be tested more than once. So I use the Seek to check it.
e.g. if a panel ID is 1234-5, it will be 1234-5 (1), 1234-5 (2) and so on in panelID field of the tables,
since I have to use unique panelID to combine two sets of testing data collected in two tables to get
a required query table.

If you need the whole VBA code, let me know how to attach it and send to you.

Many thanks.

0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12387953
I see - so you just need to get all of the records where the key starts with the specified panel ID? You could retrieve the dataset you need with this query:

"SELECT * FROM MyTable WHERE MyField Like '" & panelID & "*'"
0
 

Author Comment

by:yc_yc
ID: 12388900
No, Add new records. It is the purpose of Macro.
We have to import the testing data (they are in the txt files) into the linked tables through running the Macro to add new records.

The panelID will be 1234-5 at the first time importing to its table field.
The paneID should be 1234-5 (1) at the second time importing to its table field.
The panelID should be 1234-5 (2) at the third time importing to its table field and so on if this panel tested more than once, using Seek found how many times the panel already be tested, then increasing the count to be sure the panelID always unique in its field of the table.

The problem is how to Add new records using 'db.OpenRecordset(TableToOpen, dbOpenDynaset, dbSeeChanges)' and using 'Seek' to set unique panelID?

The Macro does nothing for the Query table.
The Query table is already created like a table in the Access database.
The data in Query table are from the two sets of data collected in two tables combined by the primaryKey panelID.

Look forward to your help, thanks.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12389055
Then do this:
"SELECT Count(*) As CountOfID FROM MyTable WHERE MyField Like '" & panelID & "*'"

This will give you the number of times the panel has already been added to the table. You then simply need to run an INSERT query to add the record for this set of testing.
0
 

Author Comment

by:yc_yc
ID: 12401894
I got Compile error: "Syntax error" or "Expected: Case" if removing last double quotes ".

Following is piece of code:

    panelID = Value
    If InStr(copyline, "Panel") > 0 Then
        Set myrec = db.OpenRecordset(TableToOpen, dbOpenDynaset, dbSeeChanges)
       
        SELECT Count(*) As CountOfID FROM TableToOpen WHERE MyField Like '" & panelID & "*'"      <- This is where the error occur.
       
        Value = panelID & " " & "(" & CountOfID & ")"
    End If


Could you answer my question ASAP since I have to test it on company computer?

Thanks.
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 350 total points
ID: 12402060
No:
Set myrec = db.OpenRecordset("SELECT Count(*) As CountOfID FROM TableToOpen WHERE MyField Like '" & panelID & "*'" , dbOpenDynaset, dbSeeChanges)
0
 

Author Comment

by:yc_yc
ID: 12402441
Another Run-time error '3078':
   The Microsoft Jet database engine can not find the input table or query 'TableToOpen'.

But the linked table 'TableToOpen' existed in local Access database, what should be corrected here?

Thanks.
0
 

Author Comment

by:yc_yc
ID: 12404269
The variable TableToOpen declare as public.

If check following statement during the debug, you can see the name of the TableToOpen.
   Set myrec = db.OpenRecordset(TableToOpen, dbOpenDynaset, dbSeeChanges)

But changing the statement above to following, I got run-time error:
   Set myrec = db.OpenRecordset("SELECT Count(*) As CountOfID FROM TableToOpen WHERE MyField Like '" & panelID & "*'",
                                                  dbOpenDynaset, dbSeeChanges)

The Run-time error '3078':
   The Microsoft Jet database engine can not find the input table or query 'TableToOpen'.

What else is still incorrect? Thanks.

 
0
 
LVL 16

Assisted Solution

by:GreymanMSC
GreymanMSC earned 100 total points
ID: 12405792
Because TableToOpen may be declared as public variable in VBA, but the SQL interpreter cannot see VBA variables directly, and is looking for a Table named TableToOpen instead.  You have to pass the variable's value through string concatenation.

    Set myrec = db.OpenRecordset( _
        "SELECT Count(*) As CountOfID FROM " & TableToOpen & " WHERE MyField Like '" & panelID & "*'", _
        dbOpenDynaset, dbSeeChanges)
0
 

Author Comment

by:yc_yc
ID: 12405984
When using the following statement, the name of TableToOpen can be known. But another new error caught:
Set myrec = db.OpenRecordset( _
        "SELECT Count(*) As CountOfID FROM " & TableToOpen & " WHERE MyField Like '" & panelID & "*'", _
        dbOpenDynaset, dbSeeChanges)

Run-time error '3061':
   Too few parameters. Expected 1.

What does this error info mean and how to fix it?  Thanks a lot.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12407929
Change MyField to the field that contains the panelID data.
0
 

Author Comment

by:yc_yc
ID: 12413821
After changing MyField to the field that contains the panelID data, the run-time error gone. Thanks.

I think the function Count(*) should return a Long integer containing the number of panel tested in collection and this return integer should be assigned to variable 'CountOfID', am I right? But the 'CountOfID' is empty during debug, actually the count number should have value 2.

Below is piece of code:

MyField = "PanelID"
Set myrec = db.OpenRecordset("SELECT Count(*) As CountOfID FROM " & TableToOpen & _
                                              " WHERE " & MyField & " Like '" & panelID & "*'", _
                                                dbOpenDynaset, dbSeeChanges)        
myCount = CountOfID
Value = panelID & " " & "(" & myCount & ")"

What are still wrong here? Thanks.
0
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 350 total points
ID: 12413847
You have not referenced the recordset.

myCount=myrec!CountOfID
0
 
LVL 16

Expert Comment

by:GreymanMSC
ID: 12416601
Yeap. CountOfID is a field of the recordset, not a variable in the code. You may reference it using the bang notation above, as myrec("CountOfID") or in full:

   myCount = myrec.Fields("CountOfID").Value
0
 

Author Comment

by:yc_yc
ID: 12417655
Yes, the problem is from no reference the recordset.

Sorry, I still have a problem. See following piece of code and error msg:

  MyField = "UUT ID"
  Set myrec = db.OpenRecordset( _
        "SELECT Count(*) As CountOfID FROM " & TableToOpen & " WHERE " & MyField & " Like '" & panelID & "*'", _
        dbOpenDynaset, dbSeeChanges)

Run-time error '3075':
  Syntax error (missing operator) in query expression 'UUT ID Like '1157-2*".

The '1157-2' is the tested panelID. I have known the problem is from the space in MyField 'UUT ID'.
But the name of field has to match its name used in original text file and the testing code can't be changed by our site.

Any idea to solve it? Thanks.
0
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 350 total points
ID: 12418392
Since UUT ID has a space in it, use:

MyField="[UUT ID]"
0
 

Author Comment

by:yc_yc
ID: 12427749
Many thanks for all of your helps.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now