invalid operation after split-database

[ktimov1]

My database was working fine before an access back-end was created. I was able to click buttons on the form that took me to other forms. Now after the database was split, when I click the buttons, I get an "Invalid Operation".

Here is the code behind the button. Does something need to be changed when making a backend?

Private Sub Operating_Click()
On Error GoTo Err_Operating_Click
    Dim MyDB As Database, Mytable As DAO.Recordset, x As String
 
    Set MyDB = DBEngine.Workspaces(0).Databases(0)
    Set Mytable = MyDB.OpenRecordset("Vehicle Operating Data", DB_OPEN_TABLE)
    Mytable.Index = "VIN"
 
  DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD, , A_MENU_VER20
 
  Mytable.Seek "=", Forms![Vehicle Profile]![VIN]
 
  If Mytable.NoMatch Then
      Mytable.AddNew
      Mytable("VIN") = Forms![Vehicle Profile]![VIN]
      Mytable.Update
      LinkCriteria = "[VIN] = Forms![Vehicle Profile]![VIN]"
      DoCmd.Openform "Vehicle Operating Data", , , LinkCriteria
      DoCmd.GoToControl "[Date]"
   Else
      LinkCriteria = "[VIN] = Forms![Vehicle Profile]![VIN]"
      DoCmd.Openform "Vehicle Operating Data", , , LinkCriteria
      DoCmd.GoToControl "[Date]"
  End If
 
Exit_Operating_Click:
    Exit Sub
Err_Operating_Click:
    MsgBox Error$
    Resume Exit_Operating_Click
 
End Sub

 

[ktimov1]

I've tried changing "DB_OPEN_TABLE" to "DB_OPEN_DYNASET" and instead of invalid operation, I get "Operation is not supported for this type of object".

Any ideas??

 

[vbaInet]

I'm guessing you're not the one who developed the database?

It's bizarre for the developer to create a constant for dbOpenTable and call it pretty much the same thing DB_OPEN_Table.

What version of Access are you running?

When the error comes up and you click DEBUG what line does it highlight in yellow in the code?

 

[ktimov1]

Yes the database was created by someone else. My job was to convert it to Access 2007 and then make a backend. In the VBA no errors come when I compile the code. The error just comes when i run the database, does not give me an option to debug then, just "ok".

 

[vbaInet]

So is the exact message, "Invalid Operation" or there's more?

Try changing this line:

Set MyDB = DBEngine.Workspaces(0).Databases(0)

To this:

Set MyDB = Currentdb

Also check the references to ensure you have DAO 3.6 checked.

 

[ktimov1]

Still the same. The message was "Invalid Operation" when i used DB_OPEN_TABLE. Now I have DB_OPEN_DYNASET and the error instead is "Operation is not supported for this type of object"

 

[vbaInet]

Check the references.

 

[ktimov1]

When I try and add Microsoft DAO 3.6 Object Library, i get "Name conflicts with existing module, project, or object library"

 

[vbaInet]

Remove the older version.

By the way, how did you perform the conversion?

Also, you're not going to be running two versions of Access for that app are you?

 

[ktimov1]

The conversion was done through access. The database started in 2003 .mdb format, Through the Access office button I hit convert, and it converted/saved it as a new .accdb file. Both the front end and back end are 2007 .accdb

 

[vbaInet]

That's fine. Remove the old reference, then add the new reference and OK it.

 

[ktimov1]

Ok added Microsoft DAO 3.6 Object Library, tested the database and still had the same error when I clicked the button. "Operation is not supported for this type of object"

 

[vbaInet]

Ok, comment out the On ERROR GOTO line. With this commented out you should be able to Debug the code.

Also change this:

Dim MyDB As [COLOR=Red][B]DAO.[/B][/COLOR]Database

Run it now, click DEBUG when the message pops up and tell us which line is highlighted.

 

[ktimov1]

Ok, after debug, it highlighted "Mytable.Index = "VIN"" and when I hover over it, it says "Operation is not supported for this type of object"

 

[DCrake]

Also you cannot perform .seek on a linked table. This is why when building applications the first thing you should do is to split the application. Then you csn be sure that all operations will work as expected.

This is why you are getting the error as you cannot use the .Index as well as this is also part of the FE workspace.

 

[ktimov1]

Is there an alternative to the .seek method that will work for me?

 

[DCrake]

You could try the .Findfirst approach butr there will be some radical changes to be made to the SQL.

 

[ktimov1]

is there an alternative to the .seek method that will work for me? Ive tried Mytable.FindFirst Forms![Vehicle Profile]![VIN] instead and I still get the same error

 

[vbaInet]

I believe you need the latest Access Object library references too. Have a read:

http://allenbrowne.com/ser-38.html