Database Query

[Vijay Kotian]

I am extracting data from database by query table macro, while running macro
it connects database and it requires password to be entered every time. Is
it possible to enter password through macro, if yes, then how password can be
passed through macro?

Thank you in advance

 

[vqthomf]

Hi I Connect to my database like this cnn.Open
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & GetFromIniFile("dBPath",
"MaindB", PurMLINI) & ";Jet OLEDBatabase Password=??????;"

 

[Vijay Kotian]

The following is my code, can you suggest where I should enter Password in
this code.

Thank you

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=10.9.21.753;UID=yajivk;;APP=Microsoft
Office 2003;WSID=PSIEPK000073" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT hotel_edart.REF_NO, hotel_edart.DEALER_ID,
hotel_edart.edart_DT, hotel_edart.EXCHANGE, hotel_edart.SEG_TYPE,
hotel_edart.CLIENT_CD, hotel_edart.SCRIP_CD, hotel_edart.TRANS_TYPE,
hotel_edart.QTY" _
, _
", hotel_edart.PRICE, hotel_edart.CREATED_DT,
hotel_edart.MODIFIED_DT, hotel_edart.AUTH_DT, hotel_edart.AUCTION_QTY,
hotel_edart.AUCTION_RATE, hotel_edart.SCRIP_NAME" & Chr(13) & "" & Chr(10) &
"FROM ET.dbo.hotel_edart hotel_TRAD" _
, _
"E" & Chr(13) & "" & Chr(10) & "WHERE (hotel_edart.edart_DT>{ts '" &
tmonth1 & " 00:00:00'}) AND (hotel_edart.SEG_TYPE='C')" & Chr(13) & "" &
Chr(10) & "ORDER BY hotel_edart.EXCHANGE, hotel_edart.SCRIP_NAME" _
)
.Name = "Query from 10.9.21"
.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

 

[vqthomf]

Sorry Vijay I have never connected to an SQL server I only connect to a
MSAccess dB I understand that the password comes at the end of the connection
string.
Regards

 

[Ed Ferrero]

Hi Vijay,

The following is my code, can you suggest where I should enter Password
in
this code.

Thank you

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL
Server;SERVER=10.9.21.753;UID=yajivk;;APP=Microsoft
Office 2003;WSID=PSIEPK000073" _
, Destination:=Range("A1"))
.CommandText = Array( _

Add the password 'mypw' like this...

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=10.9.21.753;UID=yajivk;PWD=mypw;APP=Microsoft
Office 2003;WSID=PSIEPK000073" _
, Destination:=Range("A1"))

Be careful how you deploy this, the password is stored in code, and Excel
VBA code is not secure.
I would at least password protect the code, but remember that it is easy to
crack protectedVBA code.
Should be ok if the Excel workbook is kept on a secure folder.

Ed Ferrero
www.edferrero.com