VBA Access and Sql

dh_ander

Board Regular
Joined
Jul 31, 2006
Messages
119
I use the following code (just a sample) to fill a table tblFolderandFiles which is a linked table and exists in an SQL database

Code:
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

Code:
('test2','10-01-2004','300','test2.txt')"
to
Code:
('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?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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

Board Regular
Joined
Jul 31, 2006
Messages
119
NOP doesn't wotrk

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

any other suggestions?
 

dh_ander

Board Regular
Joined
Jul 31, 2006
Messages
119

ADVERTISEMENT

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

Well-known Member
Joined
Feb 20, 2004
Messages
636
Try this:
Code:
SQL = "INSERT INTO tblFoldersandFiles (map, datum, grootte, bestandsnaam)" & _
    "VALUES ('" & strMap & "','" & strDatum & "','" & dblGrootte & _
    "','" & strBestandsnaam & "')"
 

dh_ander

Board Regular
Joined
Jul 31, 2006
Messages
119

ADVERTISEMENT

solved : sql access and VBA

Thanks a million Ahnold

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

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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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

Board Regular
Joined
Jul 31, 2006
Messages
119
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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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.
 

Forum statistics

Threads
1,136,697
Messages
5,677,264
Members
419,682
Latest member
M3one

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top