Excel Access query with username/pw

[Gsimmons2005]

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

    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

 

[Guest]

You should be able to. Look at this:

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMicrosoftJet

 

[Gsimmons2005]

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?

You should be able to. Look at this:

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMicrosoftJet

--
Regards,
Tom Ogilvy

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

    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

 

[NickHK]

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

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?

You should be able to. Look at this:

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMicrosoftJet

--
Regards,
Tom Ogilvy

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

    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