Can someone shed some light on how to open an ADO connection to a
recordset in a different Access database.
Dim cnCurrent As ADODB.Connection
Dim cnOther As ADODB.Connection
Set cnCurrent = CurrentProject.Connection
Set cnTables.Open = "c:\mydb.mdb"
The code to open cnCurrent works but the code to open cnTables does
not work.
Please help!!!! I can't find any relevant examples.
On Oct 15, 11:22 am, missima...@hotmail.com wrote:
> Can someone shed some light on how to open an ADO connection to a
> recordset in a different Access database.
>
> Dim cnCurrent As ADODB.Connection
> Dim cnOther As ADODB.Connection
>
> Set cnCurrent = CurrentProject.Connection
> Set cnTables.Open = "c:\mydb.mdb"
>
> The code to open cnCurrent works but the code to open cnTables does
> not work.
>
> Please help!!!! I can't find any relevant examples.
An example ...maybe relevant ... maybe not
Dim mDefaultConnection As ADODB.connection
Private Sub OpenConnection(ByRef connection As ADODB.connection)
Set connection = New ADODB.connection
With connection
.CursorLocation = adUseClient
.Provider = "Microsoft.Jet.OLEDB.4.0"
With .Properties
.Item("Data Source") = _
"C:\Documents and Settings\Lyle Fairfield\My Documents
\Access\northwind.mdb"
End With
.Open
While (.State And adStateOpen) <> adStateOpen
Wend
End With
End Sub
Public Function DefaultConnection() As ADODB.connection
If mDefaultConnection Is Nothing Then _
OpenConnection mDefaultConnection
Set DefaultConnection = mDefaultConnection
End Function
Public Function NewConnection() As ADODB.connection
OpenConnection NewConnection
End Function
Sub test()
Dim r As ADODB.recordset
Set r = DefaultConnection.Execute("SELECT * FROM Employees")
With r
While Not .EOF
Debug.Print .Fields("FirstName").Value
.MoveNext
Wend
End With
End Sub
On Mon, 15 Oct 2007 13:53:04 -0700, lyle
wrote:
That's a nice touch. You're probably writing this because of a bad
experience? Otherwise you would just write:
..Open
debug.assert ((.State And adStateOpen) = adStateOpen)
Can you elaborate?
-Tom.
> .Open
> While (.State And adStateOpen) <> adStateOpen
> Wend
On Oct 16, 12:00 am, Tom van Stiphout
> On Mon, 15 Oct 2007 13:53:04 -0700, lyle
> wrote:
>
> That's a nice touch. You're probably writing this because of a bad
> experience? Otherwise you would just write:
> .Open
> debug.assert ((.State And adStateOpen) = adStateOpen)
>
> Can you elaborate?
>
> -Tom.
>
> > .Open
> > While (.State And adStateOpen) <> adStateOpen
> > Wend
Somewhere in the mists of time I wrote an ADO open statement (it may
not have been a connection) that needed to complete before the code
executed anymore lines. So I created the loop to wait for the open to
complete. I doubt that is necessary in VBA, but it seemed necessary in
whatever script/code I was using at the time. I tend to standardize
procedures from technology to technolgy, so I included the loop in VBA
as well.
I have been unhappy however with what I see as the "laziness" of using
a connection string, instead of being able to specify each property of
an ADO connection. While some properties (CursorLocation and Provider)
are directly available and clearly documented, others may not beeso
clearly documented or available.I discovered that one can set these
properties using the default intervening collection or qualifier
"item". Somewhere or other I ran some exploratory code to find out
what these "Item" Properties can be. (Of course there may be simpler
ways I did not discover).
Sub temp()
Dim c As ADODB.connection
Set c = New ADODB.connection
Dim o As Object
Dim z&
For z = 0 To c.Properties.Count - 1
Set o = c.Properties.Item(z)
With o
Debug.Print .Attributes, .Name, .Type, .Value
End With
Next z
End Sub
and I got:
1537 Password 8
1537 Persist Security Info 11
1537 User ID 8
1537 Data Source 8
1537 Window Handle 3
1537 Location 8
1537 Mode 3
1537 Prompt 2 4
1537 Connect Timeout 3 15
1537 Extended Properties 8
1537 Locale Identifier 3 4105
1537 Initial Catalog 8
1537 OLE DB Services 3 -5
1537 General Timeout 3
Perhaps sometime, someday it will be useful for me to be able to read
the connection's "Locale Identifier". Maybe not!
Moving right along, my code for opening an ADO SQL connection is:
Dim mDefaultConnection As ADODB.connection
Private Sub OpenConnection(ByRef connection As ADODB.connection)
Set connection = New ADODB.connection
With connection
.CursorLocation = adUseClient
.Provider = "sqloledb.1"
With .Properties
.Item("Data Source") = "Place_Holder"
.Item("Initial Catalog") = "Place_Holder"
' -----
.Item("PassWord") = "Place_Holder"
.Item("User ID") = "Place_Holder"
' -----
' OR
' -----
' .Item("Integrated Security") = "SSPI"
' -----
End With
.Open
While (.State And adStateOpen) <> adStateOpen
Wend
End With
End Sub
Public Function DefaultConnection() As ADODB.connection
If mDefaultConnection Is Nothing Then _
OpenConnection mDefaultConnection
Set DefaultConnection = mDefaultConnection
End Function
Public Function NewConnection() As ADODB.connection
OpenConnection NewConnection
End Function
I've never used Debug.Assert. Something else to add to the old
repertoire, I suppose.
Hi Lyle,
You have an interesting approach to handling this. Have you done
similar with Oracle or SAP?
Cheers
The Frog
On Oct 17, 4:57 am, The Frog
> Hi Lyle,
>
> You have an interesting approach to handling this. Have you done
> similar with Oracle or SAP?
>
> Cheers
>
> The Frog
No, sorry.