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?

 

[Andrew Fergus]

Hi David

I'm not an expert with SQL but try this :

(strMap, strDatum, dblGrootte, strBestandsnaam)

without the quotes. Does that work?

HTH, Andrew

 

[dh_ander]

NOP doesn't wotrk

gives me popup boxes to fill in the parameter.
thanks for trying

any other suggestions?

 

[Andrew Fergus]

Hi David

Does this help :

http://msdn2.microsoft.com/en-us/library/h54fa37c.aspx ?

I don't think you can use variables with the INSERT ...... VALUES syntax - how about using one of the 'FROM source' options per the linked article?

Andrew

 

[dh_ander]

Anrew it helps if you can explain to me how to include the table alias in eExpression

VALUES (eExpression1 [, eExpression2 [, ...]])
Specifies the field values to be inserted into the new record. If you omit the field names, you must specify the field values in the order defined by the table structure. If eExpression is a field name, it must include the table alias.

 

[Ahnold]

Try this:

SQL = "INSERT INTO tblFoldersandFiles (map, datum, grootte, bestandsnaam)" & _
    "VALUES ('" & strMap & "','" & strDatum & "','" & dblGrootte & _
    "','" & strBestandsnaam & "')"

 

[dh_ander]

solved : sql access and VBA

Thanks a million Ahnold

that did it (and now wondering why I dind't figure it out myselve )

Just a small question about this syntax, apart from that it works is there somewhere I can get some more info on this syntax?

 

[VicRauch]

I don't know where to get an article about this syntax, but I would like to share how I think about it, if I can make it understandable.

When we know how the SQL statement should look when it is presented to the SQL processor, which would be how it looks in the SQL view of a query, then we need to think, what must I do in the code to create a string that will look like the SQL code in the SQL view of a query.

What I do to check to see what the SQL string I'm building in code will look like is to copy the code I have written, paste it into the immediate window (or Debug window), put a question mark (?) in front of it, and press enter. The question mark means Debug.Print. This will print out the string as it will look when presented to the SQL processor.

If you don't see the single quotes around the string data, you know you need to go back to your code and put them in where they are missing. If you see the single quotes, but you see the variable names instead of data, you know that the variable names are within the double quotes, but must be outside the double quotes in order to be used as a varible that will be the actual data into the query as desired.

If this is making any sense, and you want more, I'll try to clean this up and finish it. Just ask.
HTH,

 

[dh_ander]

VicRauch, thanks

I had someone else explain it to me today (person to person) and immideately got another chalenge.
I have to read into a data base :
path
file creation date
file
size

Now guess what, I have path statements that contain single quotes for which I have to find an method to conceal this single quotes and still get them in my database (sql) at least you suggested method gives me an idea how to test.

 

[VicRauch]

David,
When having a hard time with quotes, you can "cheat" with this:
Single quote: Chr(39)
Double quote: Chr(34)
Where ever you want a single or double quote, you can use one of these to put it there. Sometimes easier than messing with the actual quotes themselves.
Test it whenever you try it.