Handling Colons (:) in Access Insert Statement

[baustin]

Hello,
I am new to this community but would like to get direction on how to insert colons ) into the database through a vba insert statement.

Here is my code sample. I am using an array with a user defined type so I can Redim Perserve the 2 dimensional array. Again the problem is that when I have any ":"s in the data the insert statements fail. Can someone tell the syntax to wrap the colon in so that it is accepted? THANKS IN ADVANCE FOR YOUR HELP!

Code sample:
For X = 1 To UBound(strLabsArray)
'MsgBox X & "_" & strLabsArray(X).Fields(1) & "_" & strLabsArray(X).Fields(2)
If strLabsArray(X).Fields(1) = "" Then Exit For

strSql = "INSERT into [tblLabs] ([Lab Code], Payer, [GL Code], TaxID, [Begin Date], [End Date], [Lab Name], Street, City, State, Zip, NPI, Clia, [Pay To], [Tax-ID])" _
& " Values ('" & strLabsArray(X).Fields(1) & "', '" & strLabsArray(X).Fields(2) & "', '" & strLabsArray(X).Fields(3) & "', '" _
& strLabsArray(X).Fields(4) & "', '" & strLabsArray(X).Fields(5) & "', '" & strLabsArray(X).Fields(6) & "', '" _
& strLabsArray(X).Fields(7) & "', '" & strLabsArray(X).Fields(8) & "', '" & strLabsArray(X).Fields(9) & "', '" _
& strLabsArray(X).Fields(10) & "', '" & strLabsArray(X).Fields(11) & "', '" & strLabsArray(X).Fields(12) & "', '" _
& strLabsArray(X).Fields(13) & "', '" & strLabsArray(X).Fields(14) & "', '" & strLabsArray(X).Fields(15) _
& "');"
CurrentDb.Execute strSql
Next

 

[MSAccessRookie]

Hello,
I am new to this community but would like to get direction on how to insert colons ) into the database through a vba insert statement.

Here is my code sample. I am using an array with a user defined type so I can Redim Perserve the 2 dimensional array. Again the problem is that when I have any ":"s in the data the insert statements fail. Can someone tell the syntax to wrap the colon in so that it is accepted? THANKS IN ADVANCE FOR YOUR HELP!

The Values being inserted appear to come from a String Array (the Dim Statement is not present), and the Columns in the Table have names that imply that they could be varying types (Strings, Numbers, Dates). If they are different types, then type conversion Functions such as cInt(), and cDate() could be required for some of the Values.

 

[baustin]

THANK YOU FOR YOUR RESPONSE! wow THAT WAS QUICK!

My Array is working fine. The problem is what 'escape characters' in my data.
What I think I am going to need is
1. a list of all possible access 'escape characters' so I can create a function that checks for these before each parameter is inserted into the database.
2. to figure out the syntax to wrap these excape character so they can be stored in the database. In the query builder I am able to insert a colon. It just wraps it in quotes when you view the sql.
(INSERT INTO test ( data ) SELECT ":" AS Expr1
I guess I can do this in VB by using """ for each quote. I will try this.......

 

[baustin]

I figured out how to do ":"s! I just wrote a routine to check for colons and wrap them in single quotes.

Now I just need to figure out how to trap for all other escape characters. AND....

I currently when I use: CurrentDb.Execute strSql. It does not raise errors. I want to raise and error with a message box so I can know when stuff like this does not happen in the system.

Thanks for your suggestions and time!
God Bless,