Opening MS Access Database with password from Excel

[ald35]

I am trying to open an MS Access Database from Excel using VBA. I am new to this, but have some experience with VB. Currently I can not open this database, I think because I have a password on the database.

For example. When you open the database from my computer first you have to enter the password to access the database. I am not sure how to bypass this automatically.

Can anyone help?

Here is my code:

Dim Db As Database
Dim Qd As QueryDef
Dim Rs As Recordset
Dim Ws As Object
Dim i As Integer
Dim Path As String

Path = "my path to db"

Set Ws = Sheets("sheet4&quot

Ws.Activate
Range("A1&quot.Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1&quot.Select

Dim wrkODBC As Workspace
Dim conODBC As Connection

Set wrkODBC = CreateWorkspace("ODBCWorkspace01", _
"pc", "enter", dbUseODBC)

Set conODBCConnection = pReportInfo.wrkODBC.OpenConnection("ODBCConnection01", dbDriverCompleteRequired, _ True, "ODBC;test;&quot

Any help would be greatly appreciated.
Thanks.
Tony

 

[Chance1234]

what version of excel and Access are you using? Filmmaker, gentlemen and East end club promoter

http://public.fotki.com/chance1234/

 

[ald35]

I am using Excel 2002 and Access 2002.

What do you recommmend?

 

[Chance1234]

is there any reason for the ODBC approach ? this is just an example i have copied from elsewhere using ADODB to access a database protected with a password,

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

With conn
.Provider = "Microsoft.Jet.OLEDB.3.51"
.ConnectionString = "datasource=C:\databases\sample.mdb,;User ID=steve;Password=steve"
.Open
End With

i can post a DAO example if you want as well




Filmmaker, gentlemen and East end club promoter

http://public.fotki.com/chance1234/

 

[Chance1234]

forgot to add is the password a database password or has user level security been set up on the databas e? Filmmaker, gentlemen and East end club promoter

http://public.fotki.com/chance1234/

 

[Chance1234]

if so here is for openign up with a database level passwor dvia ADodb

Function OpenProtectedDB(strDBPath As String, _
strPwd As String)
Dim cnnDB As ADODB.Connection

Set cnnDB = New ADODB.Connection

' Open database for shared (by default), read/write access, and
' specify database password.
With cnnDB
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Jet OLEDBatabase Password&quot = strPwd
.Mode = adModeReadWrite
.Open strDBPath
End With
' Code to work with database goes here.
cnnDB.Close
Set cnnDB = Nothing
End Function
Filmmaker, gentlemen and East end club promoter

http://public.fotki.com/chance1234/

 

[ald35]

The password is a database security password. However, the tables in the Access database are linked to an SQL Server database, therefore there is authentication when you try to run a query for the first time too..

Would it be easier to just connect to the SQL database? How would I go about doing that?

thanks.

 

[Chance1234]

to attached straight to the SQL Server database would be pretty much identical to the code above, except the path would be to the SQL Server drive and the Jet Reference would be a reference to SQL Server, Filmmaker, gentlemen and East end club promoter

http://public.fotki.com/chance1234/

 

[ald35]

Thanks for the help. Do you know how I would connect if the SQL Server is residing on a different machine? How would I connect to a completely different machine?

This is a big help by the way. I am trying to allow my boss to get data directly from the db to excel without having to ask me.

cheers,

Tony

 

[Chance1234]

If SQL SErver is resiging on a different machine and the machine in on the network then you will need to set up a data source in control panel under data sources , referencing the SQL path. Filmmaker, gentlemen and East end club promoter

http://public.fotki.com/chance1234/