building a linked table in VBA

[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!

 

[Roger Carlson]

I see you have .AddNew, but do you also have .Update after filling the
record? General usage:

tdf.AddNew
....adds values to records
tdf.Update

Without the update, it will not save the records and will do so silently.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[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!