Does anyone know the syntax to establish a workspace/connection to query data
from an Access database that has security set up?

I've set up a special UserID and Password for this purpose.

Thanks,

VBA Dabbler

Do you mean something like this:
Set adoConn = New ADODB.Connection
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
sAccessDB & "; USER ID=Admin; PASSWORD=abcd;"

or does the database itself have a password:


http://www.able-consulting.com/MDAC/...orMicrosoftJet

Look for the section 'If MDB has a database password'

--
Regards,
Tom Ogilvy

"VBA Dabbler" wrote in message
...
Does anyone know the syntax to establish a workspace/connection to query
data
from an Access database that has security set up?

I've set up a special UserID and Password for this purpose.

Thanks,

VBA Dabbler

Tom,
Yes. However, the database is on a network share and so is the workgroup
file. I browsed your site - the closest match I found to my situation is "If
using a Workgroup (System Database)" under "OLE DB Provider for Microsoft Jet
". It's below:

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\mydb.mdb;" & _
"Jet OLEDB:System Database=MySystem.mdw", _
"myUsername", "myPassword"

How do I point to the workgroup file? Replace the "MySystem.mdw" reference
to the path/name combination of the workgroup file?

Thanks,

VBA Dabbler

"Tom Ogilvy" wrote:

Do you mean something like this:
Set adoConn = New ADODB.Connection
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
sAccessDB & "; USER ID=Admin; PASSWORD=abcd;"

or does the database itself have a password:


http://www.able-consulting.com/MDAC/...orMicrosoftJet

Look for the section 'If MDB has a database password'

--
Regards,
Tom Ogilvy

"VBA Dabbler" wrote in message
...
Does anyone know the syntax to establish a workspace/connection to query
data
from an Access database that has security set up?

I've set up a special UserID and Password for this purpose.

Thanks,

VBA Dabbler

Perhaps someone here is an expert on this, but I know I am not. I would
recommend posting in an Access newsgroup for a more definitive answer. the
only relevance to Excel is that you are doing it through Excel VBA, but that
would be true for Word, PowerPoint, VB and so forth.

--
Regards,
Tom Ogilvy

"VBA Dabbler" wrote in message
...
Tom,
Yes. However, the database is on a network share and so is the workgroup
file. I browsed your site - the closest match I found to my situation is
"If
using a Workgroup (System Database)" under "OLE DB Provider for Microsoft
Jet
". It's below:

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\mydb.mdb;" & _
"Jet OLEDB:System Database=MySystem.mdw", _
"myUsername", "myPassword"

How do I point to the workgroup file? Replace the "MySystem.mdw"
reference
to the path/name combination of the workgroup file?

Thanks,

VBA Dabbler

"Tom Ogilvy" wrote:

Do you mean something like this:
Set adoConn = New ADODB.Connection
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
sAccessDB & "; USER ID=Admin; PASSWORD=abcd;"

or does the database itself have a password:



http://www.able-consulting.com/MDAC/...orMicrosoftJet

Look for the section 'If MDB has a database password'

--
Regards,
Tom Ogilvy

"VBA Dabbler" wrote in message
...
Does anyone know the syntax to establish a workspace/connection to
query
data
from an Access database that has security set up?

I've set up a special UserID and Password for this purpose.

Thanks,

VBA Dabbler

I have posted in an Access newsgroup, but no answer yet. You're right about
the Excel relevance - actually I'll be making the calls from Word.

Regards,
VBA Dabbler

"Tom Ogilvy" wrote:

Perhaps someone here is an expert on this, but I know I am not. I would
recommend posting in an Access newsgroup for a more definitive answer. the
only relevance to Excel is that you are doing it through Excel VBA, but that
would be true for Word, PowerPoint, VB and so forth.

--
Regards,
Tom Ogilvy

"VBA Dabbler" wrote in message
...
Tom,
Yes. However, the database is on a network share and so is the workgroup
file. I browsed your site - the closest match I found to my situation is
"If
using a Workgroup (System Database)" under "OLE DB Provider for Microsoft
Jet
". It's below:

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\mydb.mdb;" & _
"Jet OLEDB:System Database=MySystem.mdw", _
"myUsername", "myPassword"

How do I point to the workgroup file? Replace the "MySystem.mdw"
reference
to the path/name combination of the workgroup file?

Thanks,

VBA Dabbler

"Tom Ogilvy" wrote:

Do you mean something like this:
Set adoConn = New ADODB.Connection
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
sAccessDB & "; USER ID=Admin; PASSWORD=abcd;"

or does the database itself have a password:



http://www.able-consulting.com/MDAC/...orMicrosoftJet

Look for the section 'If MDB has a database password'

--
Regards,
Tom Ogilvy

"VBA Dabbler" wrote in message
...
Does anyone know the syntax to establish a workspace/connection to
query
data
from an Access database that has security set up?

I've set up a special UserID and Password for this purpose.

Thanks,

VBA Dabbler

Tom Ogilvy wrote:
the database is on a network share and so is the workgroup
file

Perhaps someone here is an expert on this

In lieu of an expert:

Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=\\ServerName\FolderName\Jet4DB.mdb;
Jet OLEDB:System Database=\\ServerName\Folder2\System_Secured_4.mdw ;
User ID=myID;
Password=mypassword;

I would
recommend posting in an Access newsgroup for a more definitive answer

I wouldn't <g. MS Access is assumed to be always connected to its
database. The average MS Access 'power' user shuns ADO and even some of
the MS Access MVPs don't seem to be able to distinguish between the MS
Access UI and the underlying Jet data engine.

Jamie.

--

It works like a charm - the second line of your suggestion was especially
helpful.

Regards,
VBA Dabbler

"Jamie Collins" wrote:


Tom Ogilvy wrote:
the database is on a network share and so is the workgroup
file

Perhaps someone here is an expert on this

In lieu of an expert:

Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=\\ServerName\FolderName\Jet4DB.mdb;
Jet OLEDB:System Database=\\ServerName\Folder2\System_Secured_4.mdw ;
User ID=myID;
Password=mypassword;

I would
recommend posting in an Access newsgroup for a more definitive answer

I wouldn't <g. MS Access is assumed to be always connected to its
database. The average MS Access 'power' user shuns ADO and even some of
the MS Access MVPs don't seem to be able to distinguish between the MS
Access UI and the underlying Jet data engine.

Jamie.

--