| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Access query with username/pw
hey guys,
So I have some code to query an external access database and that part works great. However, in that query, it connects to an oracle databse in the process and as a results prompts me for a username and password whenever I run that external query. Is there a way to program in the queried servers username/pw so I dont get a prompt or is that set on the access side? Here is my code now Code:
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=H:\Common\QRMresid\Current Month\Outputs\VTC.mdb;"
_
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("VTC Query")
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
Greg |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Access query with username/pw
You should be able to. Look at this:
http://www.carlprothman.net/Default....orMicrosoftJet -- Regards, Tom Ogilvy "Gsimmons2005" wrote: hey guys, So I have some code to query an external access database and that part works great. However, in that query, it connects to an oracle databse in the process and as a results prompts me for a username and password whenever I run that external query. Is there a way to program in the queried servers username/pw so I dont get a prompt or is that set on the access side? Here is my code now Code:
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=H:\Common\QRMresid\Current Month\Outputs\VTC.mdb;"
_
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("VTC Query")
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
Thanks Greg |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Access query with username/pw
Nothing there about 2 step password...
I have a pw to open the database and another pw so the query can link to external databse... anyone? Tom Ogilvy wrote: You should be able to. Look at this: http://www.carlprothman.net/Default....orMicrosoftJet -- Regards, Tom Ogilvy "Gsimmons2005" wrote: hey guys, So I have some code to query an external access database and that part works great. However, in that query, it connects to an oracle databse in the process and as a results prompts me for a username and password whenever I run that external query. Is there a way to program in the queried servers username/pw so I dont get a prompt or is that set on the access side? Here is my code now Code:
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=H:\Common\QRMresid\Current Month\Outputs\VTC.mdb;"
_
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("VTC Query")
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
Thanks Greg |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Access query with username/pw
When you created your link to the Oracle DB (ODBC/ORAxxx/etc) from Access,
did you not have to provide the username/PW in order to connect. There should be some option to save PW with the connection string, so Access can connect when required. Then you only need to provide the username/PW to connect to Access. I don't use Oracle, so I can't tell you exact steps. An easy way to test/generate connection string is: - Create a text file somewhere convenient. - Change it's name to Test.udl - Double click it. - Follow the wizard to connect to the DB/server etc. - When finished, open the file in a ext editor; you can then use that connection string in code. Alternatively, in Access you can do Pass-Through queries. That may be useful to you. NickHK "Gsimmons2005" wrote in message oups.com... Nothing there about 2 step password... I have a pw to open the database and another pw so the query can link to external databse... anyone? Tom Ogilvy wrote: You should be able to. Look at this: http://www.carlprothman.net/Default....orMicrosoftJet -- Regards, Tom Ogilvy "Gsimmons2005" wrote: hey guys, So I have some code to query an external access database and that part works great. However, in that query, it connects to an oracle databse in the process and as a results prompts me for a username and password whenever I run that external query. Is there a way to program in the queried servers username/pw so I dont get a prompt or is that set on the access side? Here is my code now Code:
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=H:\Common\QRMresid\Current
Code:
Month\Outputs\VTC.mdb;"
_
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("VTC Query")
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
Thanks Greg |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| refresh Query table using own password for each username | Excel Programming | |||
| Excel 2000 required username and password login for database query | Setting up and Configuration of Excel | |||
| Web Query: Username and Password. | Excel Programming | |||
| How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
| query system username | Excel Programming | |||
Linear Mode
