Problem with DB_OPEN_DYNASET after split db

[ktimov1]

Hopefully I make sense when trying to explain this.. Before I split my database into a front and backend, the code blocks listed below worked fine and the buttons on the forms did what they were suppost to do:

Dim MyDB As DAO.Database, Mytable As DAO.Recordset, x As String
    
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set Mytable = MyDB.OpenRecordset("Vehicle Financial Data", DB_OPEN_TABLE)

Mytable.Index = "PrimaryKey"
  
RunCommand acCmdSaveRecord
        
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 Financial Data", , , LinkCriteria
      DoCmd.GoToControl "[Contract Number]"
   Else
      LinkCriteria = "[VIN] = Forms![Vehicle Profile]![VIN]"
      DoCmd.Openform "Vehicle Financial Data", , , LinkCriteria
      DoCmd.GoToControl "[Contract Number]"
  End If

Dim MyDB As DAO.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"
  
RunCommand acCmdSaveRecord
    
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

However after i split the database. I had to make a few changes, like changing DB_OPEN_TABLE to DB_OPEN_DYNASET, and adding a SQL statement:

Dim MyDB As DAO.Database, Mytable As DAO.Recordset, x As String
    
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set Mytable = MyDB.OpenRecordset("Vehicle Financial Data", DB_OPEN_DYNASET)
    
Dim strSQL As String
strSQL = "SELECT * FROM [Vehicle Financial Data] WHERE VIN = '" & Me!VIN & "'"
Set Mytable = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
  
RunCommand acCmdSaveRecord
    
If Mytable.NoMatch Then
      Mytable.AddNew
      Mytable("VIN") = Forms![Vehicle Profile]![VIN]
      Mytable.Update
      LinkCriteria = "[VIN] = Forms![Vehicle Profile]![VIN]"
      DoCmd.OpenForm "Vehicle Financial Data", , , LinkCriteria
      DoCmd.GoToControl "[Contract Number]"
   Else
      LinkCriteria = "[VIN] = Forms![Vehicle Profile]![VIN]"
      DoCmd.OpenForm "Vehicle Financial Data", , , LinkCriteria
      DoCmd.GoToControl "[Contract Number]"
  End If

Private Sub Operating_Click()
On Error GoTo Err_Operating_Click
 
Dim MyDB As DAO.Database, Mytable As DAO.Recordset, x As String
    
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set Mytable = MyDB.OpenRecordset("Vehicle Operating Data", DB_OPEN_DYNASET)
    
Dim strSQL As String
strSQL = "SELECT * FROM [Vehicle Operating Data] WHERE VIN = '" & Me!VIN & "'"
 
RunCommand acCmdSaveRecord
  
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

After the split, the 1st block of code worked fine and pulled the data I needed. However for the second, it didnt pull any data out. I noticed this was because both needed data from a VIN column, however in the 1st table the VIN was the primary key, in the second table VIN isnt the primary key. Is there something in my second code that needs to be modifed?

Please let me know if this needs further explaination, or if my explaination makes sense at all...

 

[pbaldy]

Well, in the second you don't actually use strSQL. In the first, you set Mytable to the table and then turn right around and set it to strSQL, which makes no sense. How would you describe in words what you're trying to accomplish here?

 

[ktimov1]

Your right, I see where the mistake is, I set mytable twice.

Set MyDB = DBEngine.Workspaces(0).Databases(0)
 
Dim strSQL As String
 
strSQL = "SELECT * FROM [Vehicle Operating Data] WHERE VIN = '" & Me!VIN & "'"
Set Mytable = MyDB.OpenRecordset(strSQL, dbOpenDynaset)

This now works. Thanks a lot!