VBS file to query password protected MDB

Using Office 2003 and Windows XP;

I have a VBS file that I am using to query an MS-Access DB and retrieve some
data. The MDB is password protected; I get an error on the last line, so I
know it has to do with the password syntax. Can someone help me out to
correct the syntax so I can query the DB?

sFullNameSource = "\\myUNC\folder\folder\FileName.mdb"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Provider = "Microsoft.Jet.OLEDB.4.0;"
cn.Open "Data Source = " & sFullNameSource & "; Password = 123"

Thanks much in advance.

Richard
Fri Apr 06 10:52:33 CDT 2007

XP wrote:

> Using Office 2003 and Windows XP;
>
> I have a VBS file that I am using to query an MS-Access DB and retrieve
> some
> data. The MDB is password protected; I get an error on the last line, so I
> know it has to do with the password syntax. Can someone help me out to
> correct the syntax so I can query the DB?
>
> sFullNameSource = "\\myUNC\folder\folder\FileName.mdb"
> Set cn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> cn.Provider = "Microsoft.Jet.OLEDB.4.0;"
> cn.Open "Data Source = " & sFullNameSource & "; Password = 123"

To be safe, I would remove spaces. If that doesn't help, perhaps the 123 is
taken as a number. Try:

sPassword = "123"
cn.Open "DataSource=" & sFullNameSource & ";Password=" & sPassword

Actually, I use:

rs.ActiveConnection = "Microsoft.Jet.OLEDB.4.0;" _
"DataSource=" & sFullNameSource & ";Password=" & sPassword
rs.Source = sQuery
rs.Open

where sQuery is my SQL query and sFullNameSource is the full path and file
name of the mdb. My mdb's are not password protected, so maybe you also need
to specify "User ID=", as in:

rs.ActiveConnection = "Microsoft.Jet.OLEDB.4.0;" _
"DataSource=" & sFullNameSource & ";User ID=" & sUser & ";Password=" &
sPassword

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

Bob
Fri Apr 06 15:43:25 CDT 2007

XP wrote:
> Using Office 2003 and Windows XP;
>
> I have a VBS file that I am using to query an MS-Access DB and
> retrieve some data. The MDB is password protected; I get an error on
> the last line, so I know it has to do with the password syntax. Can
> someone help me out to correct the syntax so I can query the DB?
>
> sFullNameSource = "\\myUNC\folder\folder\FileName.mdb"
> Set cn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> cn.Provider = "Microsoft.Jet.OLEDB.4.0;"
> cn.Open "Data Source = " & sFullNameSource & "; Password = 123"
>
A very good reference is www.connectionstrings.com. If you look there, you
will find this connection string to use:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet
OLEDB:Database Password=MyDbPassword;

Never use the "Password=" attribute with Jet databases that are not
protected with workgroup security.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"