Jackyan
04-15-2013, 01:42 PM
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
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