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

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" wrote:

> 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

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" wrote:

> 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" wrote:
>
> > 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

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

"Vijay Kotian" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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

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