Querying Data from Secured Access Database with VBA

[Guest]

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]

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/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMicrosoftJet

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

 

[Guest]

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]

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.

 

[Guest]

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

 

[Jamie Collins]

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.

--

 

[Guest]

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

Regards,
VBA Dabbler

 

[Tom Ogilvy]

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

what Jamie Provided:
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;

is Jet4DB.mdb a place holder like mydb.mdb or does this actually have
special relevance - it has to be Jet4DB.mdb?
( The OP pointed out this line was particularly interesting. )

Is there something special between FolderName and Folder2 or are these
again, just representative placeholders?

Jamie used UserID=, Password=. while the linked example didn't. Must they
be included.

Is System_Secured_4.mdw a literal name that must be used or is it a
placeholder like mysystem.mdw in the link example?

Thanks if you can enlighten me.

 

[Jamie Collins]

is Jet4DB.mdb a place holder like mydb.mdb or does this actually have
special relevance

Tom, The answer to all you questions is, they are all placeholders for
the real .mdb, .mdw, folder names, etc and for my example I made up my
own placeholder values. I must have overlooked the original example
connection string posted because I usually use the OP's values where
supplied. Sorry for the confusion.

Jamie.

--

 

[Guest]

I have two solutions that work. One uses the DAO and the other the ADO
library.

The DAO solution is (from the Access.Security Newsgroup):

Dim DaoDBEngine As Object '(All others are string declarations)

Set DaoDBEngine = CreateObject("DAO.DBEngine.36")
DaoDBEngine.SystemDB = strDBWorkgroupPathName
DaoDBEngine.DefaultUser = strDatabaseUserID
DaoDBEngine.DefaultPassword = strDatabasePassword

Set CurrentDB = DaoDBEngine.OpenDatabase(strDatabasePathName)

The ADO solution is a combination of Tom's and Jamie's input, and previous
code I used to connect to a secured Oracle database. It is:

Dim Cnn As New ADODB.Connection '(All others are string declarations)

strProviderName = "Microsoft.Jet.OLEDB.4.0"
strDatabasePath = "\\servername\DBPath"
strDatabaseName = "DdatabaseName.mdb"
strDatabasePathName = strDatabasePath & "\" & strDatabaseName
strDBWorkgroupFilePath = "\\servername\WrkGrpPath"
strDBWorkgroupFileName = "WorkgroupFileName.mdw"
strDBWorkgroupPathName = strDBWorkgroupFilePath & "\" &
strDBWorkgroupFileName

strUserID = "A_User_ID"
strUserPassword = "password"

strConnString = "Provider=" & strProviderName & ";" & _
"Data Source=" & strDatabasePathName & ";" & _
"Jet OLEDB:System Database=" & strDBWorkgroupPathName &
";" & _
"User ID=" & strUserID & ";" & _
"Password=" & strUserPassword
Cnn = strConnString
Cnn.Open

Hopefully this is clear.

Thanks for your help, Tom and Jamie.

Regards,
VBA Dabbler