Accessing Access data from other applications w VBA

[Guest]

I seem to be unable to figure out the syntax to allow me to get at Access
data from other programs (in this case Outlook). In this case, I have
selected the Access 11 library and Microsoft Active X 2.7 library as
references. The table in question is called "client" in a database called
"legal files.mdb" Here is what I have so far:

********************************************
Sub GetAccessData()

Dim AccessApp As Access.Application
Set AccessApp = New Access.Application

AccessApp.OpenCurrentDatabase "c:\access\legal files.mdb"

Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection

Dim myRecordset As New ADODB.Recordset
myRecordset.ActiveConnection = myConnection
myRecordset.Open "[Client]", , adOpenStatic, adLockOptimistic

Dim TotalRecords As String
Dim Records As Long

Records = myRecordset.RecordCount
TotalRecords = Records

MsgBox ("Total Records in Client: " + Records)

End Sub
**********************************************

What am I missing (not that I really understand this or anything). Any help
would be appreciated.

TIA

 

[Brendan Reynolds]

You don't need any Access objects (objects in the Access 11 object library)
for this, you can do it all with ADO or DAO.

CurrentProject only works from within Microsoft Access, to access (no pun
intended) data using ADO outside of Access, you'll have to create and
configure the ADO Connection yourself.

Public Sub GetJetData()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Brendan Reynolds\My
Documents\Northwind.mdb;" & _
"Persist Security Info=False"
Set rst = New ADODB.Recordset
cnn.Open
rst.Open "SELECT * FROM Categories", cnn
Do Until rst.EOF
Debug.Print rst.Fields("CategoryName")
rst.MoveNext
Loop
rst.Close
cnn.Close

End Sub

The easiest way to get the connection string right is to use the Universal
Data Link (UDL) dialog. Recent versions of Windows don't provide a direct
means of creating a new UDL file, so create a text file and then change its
extension to "UDL". Then double click it to open it in the UDL dialog. It
defaults to using the SQL Server provider, so remember to change that. When
you've created and tested the UDL using the dialog, open it in Notepad and
copy the connection string.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I seem to be unable to figure out the syntax to allow me to get at Access
data from other programs (in this case Outlook). In this case, I have
selected the Access 11 library and Microsoft Active X 2.7 library as
references. The table in question is called "client" in a database called
"legal files.mdb" Here is what I have so far:

********************************************
Sub GetAccessData()

Dim AccessApp As Access.Application
Set AccessApp = New Access.Application

AccessApp.OpenCurrentDatabase "c:\access\legal files.mdb"

Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection

Dim myRecordset As New ADODB.Recordset
myRecordset.ActiveConnection = myConnection
myRecordset.Open "[Client]", , adOpenStatic, adLockOptimistic

Dim TotalRecords As String
Dim Records As Long

Records = myRecordset.RecordCount
TotalRecords = Records

MsgBox ("Total Records in Client: " + Records)

End Sub
**********************************************

What am I missing (not that I really understand this or anything). Any
help would be appreciated.

TIA

 

[Guest]

Thank you. That works perfectly.

You don't need any Access objects (objects in the Access 11 object
library) for this, you can do it all with ADO or DAO.

CurrentProject only works from within Microsoft Access, to access (no pun
intended) data using ADO outside of Access, you'll have to create and
configure the ADO Connection yourself.

Public Sub GetJetData()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Brendan Reynolds\My
Documents\Northwind.mdb;" & _
"Persist Security Info=False"
Set rst = New ADODB.Recordset
cnn.Open
rst.Open "SELECT * FROM Categories", cnn
Do Until rst.EOF
Debug.Print rst.Fields("CategoryName")
rst.MoveNext
Loop
rst.Close
cnn.Close

End Sub

The easiest way to get the connection string right is to use the Universal
Data Link (UDL) dialog. Recent versions of Windows don't provide a direct
means of creating a new UDL file, so create a text file and then change
its extension to "UDL". Then double click it to open it in the UDL dialog.
It defaults to using the SQL Server provider, so remember to change that.
When you've created and tested the UDL using the dialog, open it in
Notepad and copy the connection string.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I seem to be unable to figure out the syntax to allow me to get at Access
data from other programs (in this case Outlook). In this case, I have
selected the Access 11 library and Microsoft Active X 2.7 library as
references. The table in question is called "client" in a database called
"legal files.mdb" Here is what I have so far:

********************************************
Sub GetAccessData()

Dim AccessApp As Access.Application
Set AccessApp = New Access.Application

AccessApp.OpenCurrentDatabase "c:\access\legal files.mdb"

Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection

Dim myRecordset As New ADODB.Recordset
myRecordset.ActiveConnection = myConnection
myRecordset.Open "[Client]", , adOpenStatic, adLockOptimistic

Dim TotalRecords As String
Dim Records As Long

Records = myRecordset.RecordCount
TotalRecords = Records

MsgBox ("Total Records in Client: " + Records)

End Sub
**********************************************

What am I missing (not that I really understand this or anything). Any
help would be appreciated.

TIA