Multiple SQL Statments from Excel to Access

[aabnormal]

Hello All -


I am attempting to have my code run a series of UPDATE statments keyed off of a list of data in my Excel sheet that update my access database. I cannot figure out what is the best way to accomplish this as the excel data will change each time the code is run. below is a sample of the code i am working with that includes a few of the different angles i have attempted. Please let me know of course if there is any more information I can supply. Thanks.


Sub UpdateSomeRecordsADO()
Dim cnn As ADODB.Connection
Dim UpdCommand As ADODB.Command
Dim dbstrg As String
Dim UpdStrg As String

dbstrg = ""
dbstrg = dbstrg & "O:\AP\2008\testdb.mdb"
' dbstrg = dbstrg & Cells(2, 12)
' dbstrg = dbstrg & "\North American "
' dbstrg = dbstrg & Cells(2, 11)
' dbstrg = dbstrg & ".mdb"

'' Open the connection.
Set cnn = New ADODB.Connection
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Open dbstrg

'' Set the command text.
Set UpdCommand = New ADODB.Command
Set UpdCommand.ActiveConnection = cnn

'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UpdStrg = ""
UpdStrg = UpdStrg & "UPDATE [Sheet1] SET [Paid] = 'Confirmed' WHERE [Acct] = 00003 AND [T/D] = 20080512 AND [B/S] = 1 AND [Price] = 1388.25"

' vtSql = vtSql & " UPDATE " & "Sheet1"
' vtSql = vtSql & " SET Salaryx= 24680"
' vtSql = vtSql & " WHERE Namex='Jennings'"

With UpdCommand
.CommandText = UpdStrg
.CommandType = adCmdText
.Execute
End With
' Loop
'Range("Q2").Select

' Do
' UpdCommand.Execute
' ActiveCell.Offset(1, 0).Select
' Loop Until IsEmpty(ActiveCell.Offset(0, -16))


'' Close the connections and clean up.
cnn.Close
Set UpdCommand = Nothing
Set cnn = Nothing
End Sub

 

[docjohn52]

abby-normal,

I have went around and around getting the quotes right on SQL statements in VB.

The following works for vb6 and probably for vba as well, and you will notice the single and double quotes surrounding text2.text.

In this working instance, the clientID is defined as a long integer and is the primary key for the table and text2.text actually is text2.text="123456". Even though the long int <> text2.text which is a string, it is equal as far as SQL is concerned and again this statement works.

in this instance SQL itself is looking for a numeric variable, and you could probaly replace text2.text with ltrim(str(val("123456")))

Set rst = dbsDebit.OpenRecordset("SELECT * FROM creditors WHERE clientID = " & Text2.Text & "", dbOpenDynaset, ReadOnly)


This is the statement used to find an actual text string being held by "text2.text" - i.e. "Johnny" where SQL is looking for a string, not numeric data to match...

Set rst = dbsDebit.OpenRecordset("SELECT * FROM seclev WHERE userID = """ & Text2.Text & """", dbOpenDynaset, ReadOnly)

This problem mutliples itself with one AND in the query, and I worked many hours to get one syntaxed correctly.

Don't know about apostophes working with access, I know they do for MS-SQL. I KNOW access will use " or # for an SQL delimiter.

If I have some time I'll play with it a little this weekend...

John