OpenRecordset problem

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



yc_ycAsked:
Who is Participating?
 
shanesuebsahakarnConnect With a Mentor Commented:
No:
Set myrec = db.OpenRecordset("SELECT Count(*) As CountOfID FROM TableToOpen WHERE MyField Like '" & panelID & "*'" , dbOpenDynaset, dbSeeChanges)
0
 
shanesuebsahakarnConnect With a Mentor Commented:
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
 
flavoCommented:
Do we need to use ADO with MS SQL?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
GreymanMSCConnect With a Mentor Commented:
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
 
shanesuebsahakarnCommented:
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
 
Alan WarrenApplications DeveloperCommented:
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
 
yc_ycAuthor Commented:
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
 
flavoConnect With a Mentor Commented:
In VBA editor go Tools - Refrecnes and Tick Microsoft DAO Obj. Library 3.?
0
 
GreymanMSCCommented:
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
 
yc_ycAuthor Commented:
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
 
shanesuebsahakarnConnect With a Mentor Commented:
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
 
yc_ycAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
 
flavoCommented:
agreed
0
 
Alan WarrenApplications DeveloperCommented:
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
 
yc_ycAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
 
yc_ycAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
 
yc_ycAuthor Commented:
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
 
yc_ycAuthor Commented:
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
 
yc_ycAuthor Commented:
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
 
GreymanMSCConnect With a Mentor Commented:
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
 
yc_ycAuthor Commented:
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
 
shanesuebsahakarnCommented:
Change MyField to the field that contains the panelID data.
0
 
yc_ycAuthor Commented:
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
 
shanesuebsahakarnConnect With a Mentor Commented:
You have not referenced the recordset.

myCount=myrec!CountOfID
0
 
GreymanMSCCommented:
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
 
yc_ycAuthor Commented:
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
 
shanesuebsahakarnConnect With a Mentor Commented:
Since UUT ID has a space in it, use:

MyField="[UUT ID]"
0
 
yc_ycAuthor Commented:
Many thanks for all of your helps.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.