Password in mdb open from excel

[dtutton]

Im trying to import data from a password protected access database from excel vba.

Im using the lines:

Dim DB1 As Database
Dim RS1 As Recordset
On Error GoTo ErrorHandler
Set DB1 = OpenDatabase("c:\temp\Test.MDB&quot
Set RS1 = DB1.OpenRecordset("Testtable", dbOpenDynaset)
With RS1

but need to enter a password for the database. Can anyone help me on the syntax - I vcant find in documentation.

Thanks,

David

 

[raskew]

This snippet should get you up to the point of doing things with your database


Sub OpenDB()
Dim db As DATABASE
Dim ws As Workspace
Dim rs As Recordset
Dim mymdb As String
Dim mypwd As String
Dim n as integer

mymdb = "c:\temp\Test.MDB"
'replace trim9 with your password
mypwd = "MS Access;PWD=trim9"
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(mymdb, False, False, mypwd)
Set rs = db.OpenRecordset"Testtable", dbOpenDynaset)

'test for empty recordset
if not rs.bof then
n = 0
rs.movelast
rs.movefirst
n = rs.recordcount
if n > 0 then
with rs
' do something
end if
end if
rs.close
db.close
set db = nothing
end sub

 

[raskew]

I may have spoken to quickly. The previous response will work from within Access but can't guarantee it won't require some modification when calling from Excel.

 

[dtutton]

Perfect, only line changed was:

Set DB = OpenDatabase(mymdb, False, False, mypwd)

Thanks alot