Append data to a table from VBA 1

[riny]

Below is the code I am stuck on (the sql is clearly not the right way to go, there ought to be a "FROM" bit). The "rem" is the bit where I need help --I remmed it because it doesn't work.

I am trying to append data into an existing table, based on data created in vba (after picking up document names in a directory).
Here is the code I have:

Dim strDocname, strLocation

strLocation = "xxnotrelevant"

strDocname = Dir(strLocation)
Do While strDocname <> &quot;&quot;
If strDocname <> &quot;.&quot; And strDocname <> &quot;..&quot; Then
'Parse the document name to get the data needed, where document name saved is in the format &quot;Neumonic=CoordID-RefID+Description.doc&quot; ignoring &quot;neumonic&quot; which is just for visual help
Dim CoordID, RefId, strDocdescription, PosCoord, PosRef, PosDescription
PosCoord = InStr(1, strDocname, &quot;=&quot + 1
PosRef = InStr(1, strDocname, &quot;-&quot + 1
PosDescription = InStr(1, strDocname, &quot;+&quot + 1
CoordID = Mid(strDocname, PosCoord, PosRef - 1 - PosCoord)
RefId = Mid(strDocname, PosRef, PosDescription - 1 - PosRef)
strDocdescription = Mid(strDocname, PosDescription, Len(strDocname) - 3 - PosDescription)
'now append to table
Dim db As Database, strSQL, strTargettable
strTargettable = &quot;Coordinate Correspondance&quot;
Set db = CurrentDb
Rem strSQL = &quot;INSERT INTO [Coordinate Correspondance] ( [Reference ID], [Coordinate ID], [Description] ) SELECT RefID, CoordID, strDocdescription;&quot;
db.Execute strSQL
End If
strDocname = Dir
Loop

I clearly don't know how to bring the data I have in hand in VBA into a table. Would sure appreciate some help.

Thanks
Riny

 

[THoey]

Why not use the ADDNEW method? Here is an example from the help:

The following example creates a new record in an Employees table and saves the changes:

Sub AddNewRecord(ByVal rst As Recordset, _
strLast As String, strFirst As String)

With rst
' Add new record.
.AddNew

' Add data.
!LastName = strLast
!FirstName = strFirst

' Save changes.
.Update
End With
End Sub Terry M. Hoey

 

[riny]

Thanks very much Terry!
Under time pressure, I have been perusing everything in the msaccess forums here for inspriration and ideas, indeed I fell upon the addnew method somewhere and adapted it to my needs.
Again thanks Terry, had I been less impatient and had your reply I would have understood that was the way to go and saved a lot of time.

In case anyone is interested here is the coding that gives me what I needed:

______________________________________________
Private Sub CommandRefresh_Click()

'Gets word files from strLocation, tests for filename format &quot;Neumonic=CoordID-RefID+Description&quot;
'and appends a RefID row with CoordID and Description if not already present in strTargettable (RefID is unique)
'and requeries the subform to show word documents for the current coordinate.

Dim strDocname As String, strLocation As String, strLocation2 As String, strTargettable As String, strDocdescription As String
Dim CoordID As Long, RefId As Long, PosCoord As Integer, PosRef As Integer, PosDescription As Integer
Dim db As Database, rs As Recordset

strTargettable = &quot;Coordinate Correspondance&quot;
Set db = CurrentDb
Set rs = db.OpenRecordset(strTargettable, dbOpenDynaset)
strLocation = DLookup(&quot;[Location]&quot;, &quot;User&quot & &quot;\wordlib\mail\&quot; & DLookup(&quot;[Category]&quot;, &quot;Coordinate Category&quot;, &quot;[Category ID] = Form.[Category ID]&quot & &quot;\&quot;
strLocation2 = strLocation & &quot;*.doc&quot;

'get document names
strDocname = Dir(strLocation2)

Do While strDocname <> &quot;&quot;
If strDocname <> &quot;.&quot; And strDocname <> &quot;..&quot; Then
'Parse the document name to get the data needed
PosCoord = InStr(1, strDocname, &quot;=&quot;, vbTextCompare) + 1
PosRef = InStr(1, strDocname, &quot;-&quot;, vbTextCompare) + 1
PosDescription = InStr(1, strDocname, &quot;+&quot;, vbTextCompare) + 1
' check if document name is in correct format if not ignore document
If PosCoord = 1 Or PosRef = 1 Or PosDescription = 1 Then
GoTo ProcessNext
End If
CoordID = Mid(strDocname, PosCoord, PosRef - 1 - PosCoord)
' check if coordinate exists if not ignore document
If IsNull(DLookup(&quot;[Coord ID]&quot;, &quot;Coordinates&quot;, &quot;[Coord ID] = &quot; & CoordID)) Then
GoTo ProcessNext
End If
RefId = Mid(strDocname, PosRef, PosDescription - 1 - PosRef)
strDocdescription = Mid(strDocname, PosDescription, Len(strDocname) - 3 - PosDescription)
'now append to table if not already present
If IsNull(DLookup(&quot;[DocRef ID]&quot;, strTargettable, &quot;[DocRef ID] = &quot; & RefId)) Then
rs.LockEdits = True
rs.AddNew
rs(&quot;DocRef ID&quot = RefId
rs(&quot;Coord ID&quot = CoordID
rs(&quot;DocDescription&quot = strDocdescription
rs(&quot;DocPath&quot = strLocation & strDocname
rs.Update
rs.LockEdits = False
End If
ProcessNext:
End If
strDocname = Dir
Loop

rs.Close
db.Close
Forms![Coordinates form]![Coordinates Correspondance subform].Requery

End Sub