OpenRecordset problem

Do we need to use ADO with MS SQL?

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.

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

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

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. ;)

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.
 

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.

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.

agreed

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

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.

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 & "*'"

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.

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.

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.

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.

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.

 

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.

Change MyField to the field that contains the panelID data.

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.

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

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.

Many thanks for all of your helps.