Solved

OpenRecordset problem

Posted on 2004-10-20
31
7,964 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
Comment Utility
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
Comment Utility
Do we need to use ADO with MS SQL?
0
 
LVL 16

Assisted Solution

by:GreymanMSC
GreymanMSC earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
In VBA editor go Tools - Refrecnes and Tick Microsoft DAO Obj. Library 3.?
0
 
LVL 16

Expert Comment

by:GreymanMSC
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
agreed
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
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
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
No:
Set myrec = db.OpenRecordset("SELECT Count(*) As CountOfID FROM TableToOpen WHERE MyField Like '" & panelID & "*'" , dbOpenDynaset, dbSeeChanges)
0
 

Author Comment

by:yc_yc
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Change MyField to the field that contains the panelID data.
0
 

Author Comment

by:yc_yc
Comment Utility
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
Comment Utility
You have not referenced the recordset.

myCount=myrec!CountOfID
0
 
LVL 16

Expert Comment

by:GreymanMSC
Comment Utility
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
Comment Utility
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
Comment Utility
Since UUT ID has a space in it, use:

MyField="[UUT ID]"
0
 

Author Comment

by:yc_yc
Comment Utility
Many thanks for all of your helps.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

744 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

11 Experts available now in Live!

Get 1:1 Help Now