VBA Error after splitting Access2K Database

[KJ0281]

I am building off of a database that someone else built and there is a problem. When I split the database and distribute it, this piece of code stops working. I used the splitter utility and everything is fine so I don't know why this is a problem. It works when the database is not split. At the line" rst.Index = "Primarykey" " the compiler will not recognize the .(dot) operator for rst. Any ideas?

Sub UpdateSystemDescriptions()
' Update Hardware Description
txtBARCO_Hardware = cboBARCO_Hardware.Value
Dim rst As RecordsetSet
rst = CurrentDb.OpenRecordset("Hardware Products&quot
rst.Index = "Primarykey"
rst.Seek "=", cboBARCO_Hardware.Value
If rst.NoMatch Then
lblBARCO_Hardware.Caption = "Product not found."
Else
If IsNull(rst![Description]) Then
lblBARCO_Hardware.Caption = "No description given."
Else
lblBARCO_Hardware.Caption = rst![Type] & " - " & rst![Description]
End If
End If
End Sub

 

[Databaseguy]

Setrst = CurrentDb.OpenRecordset("Hardware Products&quot

 

[KJ0281]

Thank you. I added Set, but still have the same problem. Any thing else? Is the (.) operator not supported in split databases? If they aren't, how do I preform this function?

Thanks again.

 

[Databaseguy]

RecordsetSet should be Recordset

It seems that the Index property is not supported for a linked table. I ran your code for a linked table with an index, a linked table wothout an index and a local table with an index. The only table it worked for was the local one.

Looks like your going to have to use a query, or open a recordset based on a SQL statement instead.

 

[Databaseguy]

Update, I found some code that allow the use of the seek method on a linked table. This is straight out of the Sybex book.

Sub SeekLocalOrLinkedDAO(ByVal strTable As String, _
ByVal strCompare As String, _
Optional ByVal strIndex As String = "PrimaryKey&quot

' Performs DAO Seek on table using the specified
' index and search criteria. Works with both
' local and linked Access tables.
'
' From Access 2000 Developer's Handbook, Volume II
' by Litwin, Getz, and Gilbert. (Sybex)
' Copyright 1999. All Rights Reserved.
'
' In:
' strTable: Name of table
' strCompare: Comma delimited list of search values
' strIndex: Name of index. Default is "PrimaryKey"
' Out:
' Prints to the debug window list of field values
' or 'No match was found'.

Dim db As dao.Database
Dim rst As dao.Recordset
Dim fld As dao.Field
Dim strConnect As String
Dim strDb As String
Dim intDBStart As Integer
Dim intDBEnd As Integer

Const adhcDB = "DATABASE="

Set db = CurrentDb
' Grab connection string from tabledef
strConnect = db.TableDefs(strTable).Connect

' If connection string is "" then it's a local table.
' Otherwise, need to parse database portion of
' connection string.
strDb = ""
If Len(strConnect) > 0 Then
intDBStart = InStr(strConnect, adhcDB)
intDBEnd = InStr(intDBStart + Len(adhcDB), _
strConnect, ";&quot
If intDBEnd = 0 Then intDBEnd = Len(strConnect) + 1
strDb = Mid(strConnect, intDBStart + Len(adhcDB), _
intDBEnd - intDBStart)

' Open the external database.
Set db = DBEngine.Workspaces(0).OpenDatabase(strDb)
End If

' Need to open a table-type recordset to use Seek.
Set rst = db.OpenRecordset(strTable, dbOpenTable)
rst.Index = strIndex

rst.Seek "=", strCompare

If Not rst.NoMatch Then
' This example is just printing out the
' values of each of the fields of the
' found record, but you get the idea...
For Each fld In rst.Fields
Debug.Print fld.Name & ": " & fld.Value
Next
Else
Debug.Print "No match was found."
End If

Set fld = Nothing
rst.Close
Set rst = Nothing
If Len(strDb) > 0 Then
db.Close
End If
Set db = Nothing
End Sub