Open a database with username/password supplied

[piyushdabomb]

Hi Gurus,

Currently, I have several PROCEDURES where I am able to successfully open a databse using the OpenDatabase method. The problem is that I have to constantly supply a password. Is there a way where I can bypass this?

The current syntax:

Set Open_DB2 = Workbooks.OpenDatabase(filename:="C:\" & numstuds_total)

I tried using the following syntax:

Set G_Workspace = DBEngine.Workspaces(0)
Set G_Database = G_Workspace.OpenDatabase("C:\" & numstuds_total, False, False, "MS Access;PWD=" & G_password)

but I keep getting an "Object Required" error.

Any help is greatly appreciated.

I looked into a similar thread but using the subscribers syntax, I am not able to get it to work successfully.

http://www.tek-tips.com/viewthread.cfm?qid=1388082&page=9

 

[MikeGeitner]

Here's one of mine, thanks to this forum. See the Form_Load event. Maybe this will help.

Option Compare Database
Option Explicit

Dim db As DAO.Database
Dim strConnection As String
Dim ws As Workspace



Private Sub Form_Load()

Set ws = DBEngine.Workspaces(0)
Let strConnection = "ODBC;DSN=" & "jobboss32" & ";UID=" & "Support" & ";PWD=" & "lonestar"
Set db = ws.OpenDatabase("", False, False, strConnection)

End Sub

 

[piyushdabomb]

I have a macro which uses the opendatabase method to run a "Microsoft Query" .dqy file. Whenever I run the macro, I am prompted for a Username and password in the "Microsoft ODBC for Connect" window where I can then successfully use the SQL information the .dqy file to run my query and return it back to excel.

The issue I am currently have is to bypass the password connection for this .dqy file. What I am trying to do is run this statement:

Code:
numstuds_total = "SQL Queries for Compliance Report\# of students past due-MDR-PEH-CAPA.dqy"

Set Open_DB2 = Workbooks.OpenDatabase(Filename:="C:\" & numstuds_total)


without having to keep placing in the password. The alternative I tried was to open the connection to the database and then try to open the dqy file, but I am still prompted for a password.

Code:
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection

With objConn
.Provider = "msdaora"
.ConnectionString = "DSN=GTMSP;"
.Open "Provider = msdaora; Data Source = GTMSP; User Id = PLATEAU; Password = xxxxxx"

End With

If I use the information you provided above, I receive the error message "Run-time Error 429 ActiveX component can't create object"

Here is the code:

Option Explicit
'Option Compare Database


Dim db As DAO.Database
Dim strConnection As String
Dim ws As Workspace

Private Sub Form_Load()

Set ws = DBEngine.Workspaces(0)
Let strConnection = "ODBC;DSN=" & "GTMSP" & ";UID=" & "PLATEAU" & ";PWD=" & "xxxxx"
Set db = ws.OpenDatabase("", False, False, strConnection)

End Sub