MS Excel VBA and SQL – problem with INSERT and UPDATE statem

[TKrump]

MS Excel VBA and SQL – problem with INSERT and UPDATE command

Could somebody help me with a little (?) problem?
I need to construct a procedure that should connect MS Access database and INSERT or UPDATE a data record into a table.

I know, SELECT statements are going very well there by using functions supported by XLODBC.xla. In Help there is written that INSERT, UPDATE or DELETE statements are also supported.
But I don’t know, which functions and syntax are needed.

In the case of SELECT statements I use following procedure (a simple example):

strQuery = “SELECT * FROM users WHERE name = ‘Tom’” ‘specifying of the SQL expression
strDatabaseName = "Nabidky" ‘specifying of the name of the database connection
intChan = SQLOpen("DSN=" & strDatabaseName) ‘specifying of the connection ID
SQLExecQuery intChan, strQuery
Set output = Worksheets("Sheet1").Range("A10")
SQLRetrieve intChan, output, , , True
SQLClose intChan

As far as INSERT, UPDATE or DELETE I think that first four lines should be the same (with an appropriate SQL expression in strQuery). But what else? How to continue? Using them “only” I haven’t succeed.
I haven’t found anything useful in MS Help.
Thank you very much for YOUr Help.

Excuse my pure English, pleas.

Tomas

 

[dtaylor]

try adding another "&" to this line

intChan = SQLOpen("DSN=" & strDatabaseName & ) ‘specifying of the connection ID

 

[TKrump]

Thank you.
I tried your advice but unfortunately it didn’t help. I suppose I haven’t understand you well because VBA doesn’t allow me to write such a sequence at all. I also suppose that if
>>intChan = SQLOpen("DSN=" & strDatabaseName)<< runs in case of SELCT, it should be the same in case of UPDATE, INSERT etc. - connection is well defined.
I think, that the problem is that I don’t know, what should follow after SQLExecQuery. SQLRetrieve? With which parameters? Or something else?

Tomas

On 2002-10-11 16:59, dtaylor wrote:
try adding another "&" to this line

intChan = SQLOpen("DSN=" & strDatabaseName & ) ‘specifying of the connection ID

 

[dtaylor]

from what data source are you pulling your data?
access?

 

[ChrisUK]

TK.

I've attached some code I wrote a while ago which uses ODBC to get at data from an Access database. I've quickly added and tested the update function at the end as well. Hope this helps you a bit.


Private Sub CommandButton1_Click()

Dim SQLStatement
Dim ConnectString
Dim MyODBCConnection

MyODBCConnection = "ODBCtest"

' Open connection
' You need to set the ADO reference up in the Tools/Refence menu (Microsoft Actice X data objects Library)
Set ConnectDB = New ADODB.Connection
ConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=" & MyODBCConnection
ConnectDB.Open ConnectString

Set UpdateDB = New ADODB.Connection
ConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=" & MyODBCConnection
UpdateDB.Open ConnectString

' Open recordset from database table
Set rsNames = New ADODB.Recordset
rsNames.CursorLocation = adUseClient

' Use client cursor to enable AbsolutePosition property
SQLStatement = "SELECT * from testtable"
rsNames.Open SQLStatement, ConnectDB, adOpenStatic, adLockReadOnly, adCmdText

' Setup a counter to run down the spreadsheet rows
LineCount = 1

' Loop through returned records
Do While Not rsNames.EOF
Worksheets("Sheet1").Cells(LineCount, 1).Value = rsNames.Fields("CardTo")
Worksheets("Sheet1").Cells(LineCount, 2).Value = rsNames.Fields("CardFrom")
LineCount = LineCount + 1
rsNames.MoveNext
Loop

Set rsUpdate = New ADODB.Recordset
SQLStatement = "Update testtable set CardImg = 99"
rsUpdate.Open SQLStatement, UpdateDB, adOpenStatic, adLockReadOnly, adCmdText

Set UpdateDB = Nothing
Set ConnectDB = Nothing
Set rsNames = Nothing

End Sub

 

[TKrump]

Yes that‘s ACCESS. And I have reached some SUCCESS already. It seams me, that the table in the database was corrupted a little bit. So I made a new table and everything is going well. I’m sorry I bored you.
But another problem appears: SQLExecuteQuery function doesn’t support SQL statements over 250 chars. It’s very difficult to devise a solution exceeding not this limit. I hope that ACCESS (and especially EXCEL SQL functions) allows creating some views.

Thank you again. TK

On 2002-10-15 16:27, dtaylor wrote:
from what data source are you pulling your data?
access?

 

[ChrisUK]

Why use SQLExecQuery ? construct your SQL on the fly, then there is no limit.

 

[TKrump]

Ouuu, ADO! I'm affraiding of it very much because I know nothing about it ).
I have already written to dtaylor about my success with the problem - it was a corrupted table. But I will save carefully your advice and in time of troubles I will explore it particularly (I think the troubles will come very soon ) - as usually.

Thank you. TK

On 2002-10-15 17:14, ChrisUK wrote:
TK.

I've attached some code I wrote a while ago which uses ODBC to get at data from an Access database. I've quickly added and tested the update function at the end as well. Hope this helps you a bit.


Private Sub CommandButton1_Click()

Dim SQLStatement
Dim ConnectString
Dim MyODBCConnection

MyODBCConnection = "ODBCtest"

' Open connection
' You need to set the ADO reference up in the Tools/Refence menu (Microsoft Actice X data objects Library)
Set ConnectDB = New ADODB.Connection
ConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=" & MyODBCConnection
ConnectDB.Open ConnectString

Set UpdateDB = New ADODB.Connection
ConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=" & MyODBCConnection
UpdateDB.Open ConnectString

' Open recordset from database table
Set rsNames = New ADODB.Recordset
rsNames.CursorLocation = adUseClient

' Use client cursor to enable AbsolutePosition property
SQLStatement = "SELECT * from testtable"
rsNames.Open SQLStatement, ConnectDB, adOpenStatic, adLockReadOnly, adCmdText

' Setup a counter to run down the spreadsheet rows
LineCount = 1

' Loop through returned records
Do While Not rsNames.EOF
Worksheets("Sheet1").Cells(LineCount, 1).Value = rsNames.Fields("CardTo")
Worksheets("Sheet1").Cells(LineCount, 2).Value = rsNames.Fields("CardFrom")
LineCount = LineCount + 1
rsNames.MoveNext
Loop

Set rsUpdate = New ADODB.Recordset
SQLStatement = "Update testtable set CardImg = 99"
rsUpdate.Open SQLStatement, UpdateDB, adOpenStatic, adLockReadOnly, adCmdText

Set UpdateDB = Nothing
Set ConnectDB = Nothing
Set rsNames = Nothing

End Sub

 

[TKrump]

On the fly??? What does it mean? I thought that in MS Excel VBA I am absolutely dependent on it’s functions.
TK

On 2002-10-16 03:07, ChrisUK wrote:
Why use SQLExecQuery ? construct your SQL on the fly, then there is no limit.

 

[ChrisUK]

See my script above - you will notice the SQL query is created "on the fly" within the code, this way you are not limited to 127 chars!

Chris