building a linked table in VBA

J

John B. Smotherman

I'm trying to build a linked table in a VBA routine. I've stepped through the
code in debug mode, and it SEEMS to be working, except I get no results in
the table. I'm including some of the code for reference:

'create a workspace
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

'create a connection to the LocalSearchResults database
Set dbLSR = wrkJet.OpenDatabase(strDbName)

'delete the tblSearchResults table, in order to recreate it.

'if the table doesn't exist, the error handler catches the error
'and resumes at MakeTheTable, below
dbLSR.TableDefs.Delete "tblSearchResults"

MakeTheTable:
'create the tblSearchResults table, and the fields in the table
Set NewTblDef = dbLSR.CreateTableDef("tblSearchResults")
With NewTblDef
.Fields.Append .CreateField("ItemContainer", dbLong)
.Fields.Append .CreateField("TID_Number", dbLong)
.Fields.Append .CreateField("ItemName", dbText)
.Fields.Append .CreateField("Manufacturer", dbText)
.Fields.Append .CreateField("ModelNumber", dbText)
.Fields.Append .CreateField("SerialNumber", dbText)
..
..
at this point in the code I search the main table (which resides in the
back-end, separate from the local search results db) and build a collection
of records that match a specific criteria. Then I step through the collection
and add the contents to the tblSearchResults table:

'now process the search results
For Each varItem In colInventory
strItem = CStr(varItem)
varDivider = InStr(strItem, ",")
.AddNew
!ItemContainer = Val(Left$(strItem, varDivider))
!TID_Number = Val(Right$(strItem, Len(strItem) - varDivider))
!ItemName = DLookup("ItemName", "tblItems", "TID_Number = "
& lngTN)
..
..
..
It all seems to work (program flow is as expected, and I can watch the
contents of the fields change) but the tblSearchResults table is empty. What
am I missing?

Thanks for your help!
 
J

John B. Smotherman

Thanks, Roger. I do have .update but forgot to include that line in the code
snippet I pasted. However, in the meantime I figured out that I need to
refresh the link to the external table, and add the records via the link,
rather than to the external table directly. Thanks again!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top