VBA Access and Sql

[dh_ander]

I use the following code (just a sample) to fill a table tblFolderandFiles which is a linked table and exists in an SQL database

Dim rsFiles As ADODB.Recordset
Dim SQL As String
Dim count As Integer
Dim strMap As String
Dim strDatum As String
Dim dblGrootte As Double
Dim strBestandsnaam As String

strMap = "test2"
strDatum = "22-11-2000"
dblGrootte = 100000
strBestandsnaam = "test2.txt"

Set rsFiles = New ADODB.Recordset

With rsFiles
    .CursorType = adOpenDynamic
    .Open "select * from tblFoldersandFiles", CurrentProject.Connection
End With

rsFiles.MoveLast
DoCmd.SetWarnings (False)
For count = 1 To 10
dblGrootte = 1000 + count * 10
 SQL = "insert into tblFoldersandFiles (map,datum,grootte,bestandsnaam)" & _
  "values ('test2','10-01-2004','300','test2.txt')"
DoCmd.RunSQL SQL
Next count
DoCmd.SetWarnings (True)


rsFiles.Close

This code works and adds records to my table with " fixed" values
ofcourse I want to use variables.

if I change

('test2','10-01-2004','300','test2.txt')"

to

('strMap','strDatum',' dblGrootte','strBestandsnaam')

I get for every parameter a popup box to enter the value.
What's the correct syntax so it will use the parameter that have been defined a few lines before?

 

[dh_ander]

Vic thanks I alread learned that,

My challenge is if I have a string to determine
a) to detemine if the string contains a single quote
b) To mark the quote so it doesn't get caught.

cheers

 

[SydneyGeek]

David, if you need to escape a quote, just double it. So, O'Flannery becomes O''Flannery.

This is a standard SQL method.

Denis