How to test Querytable connection before actually executing SQL query

[Jackyan]

Hi,
It looks simple, I just can not figure it out, have tried serveral hours on internet to search for a solution but failed. could anybody shed some light here?

I have a little piece of code to loop thru worksheets to receive data from different tables:




For j = 1 To tn
odbctxt = "ODBC;DSN=" & subsystem(j) & ";UID=" & myid & ";PWD=" & mypwd & ";MODE=SHARE;DBALIAS=" & subsystem(j) & ";"
sheetname = wsname(j)
Worksheets(sheetname).Activate
cmdtxt = wheretxt(j)
With ActiveSheet.QueryTables.Add(Connection:=odbctxt, Destination:=Range("A2"))
.CommandText = cmdtxt
.Name = "Query1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Next j



Two questions need your help:

1. occastionally the typo in the password easilly causes the user id revoked at the remote system, so I'm just wondering if i can add one more step before the loop ( For j = 1 To tn ), to test the user id & password, if something wrong then exit the macro with some error message shown. Is there a easy way to test the connection without actaully execute a query ?

2. Is it possible to capture the SQLCODE or SQLSTATE after each SQL gets executed?

Thank you very much !

Jack

[Jan Karel Pieterse]

Using ADO you can do checks like that I expect.








Note that your current code keeps adding new querytables to your file. I wouldn't be surprised if your file has hundreds of connections in it after a while, because each time your macro runs it adds a new connection to the workbook.
Instead, you should be changing the connection property of each querytable and after that just refresh the table.