ADO Split Database

[Ra]

I am designing this new split db using ado. I have the back-end (tables) on
the common drive, and individual front ends with linked tables on multiple c
drives for various users. I have a few questions: 1) can I connect once using
the Form_Load event, then do all my data manipulations on that form? Right
now I connect on every procedure associated to that form, which is repetitive
lines of code. I tried to connect once, but then when I run the next sub it's
asking for a new connection. (is that the answer...). 2) I have a datasheet
subform in a form, and I use text boxes on the form to add/edit data in
subform. I requery the subform after edits, to see what I edited. Sometimes
it works sometimes it does not. Is this due to the slow connection? If I
close and re-open the form I can see the edit.

 

[Pieter Wijnen]

There might be "political" reasons & also maybe he (or she) is planning to
upsize

Try opening the connection using a hidden form & keep it open for the
duration

Pieter

 

[Pieter Wijnen]

I agree completely,
But policy makers like to make policies <g>

Pieter

 

[Ra]

Thank you both so much, and yes there are some polical reasons.

I agree completely,
But policy makers like to make policies <g>

Pieter

 

[Jamie Collins]

Why? If you're using a Jet back end and not planning to upsize to
SQL Server, there really is no advantage to ADO over DAO.

You seem to missed an important point: "DAO and ADO were designed to
solve two different problems" (http://support.microsoft.com/
default.aspx/kb/225048).

Consider, for example, this four-line VBA sub procedure:

Sub testADO()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open _
"SHAPE {SELECT DISTINCT C1.CustomerID" & _
" FROM Customers AS C1" & _
" INNER JOIN Orders AS O1" & _
" ON C1.CustomerID = O1.CustomerID}" & _
" APPEND ({" & _
" SELECT DISTINCT C1.CustomerID, D1.ProductID" & _
" FROM (Customers AS C1" & _
" INNER JOIN Orders AS O1" & _
" ON C1.CustomerID = O1.CustomerID)" & _
" INNER JOIN [Order Details] AS D1" & _
" ON O1.OrderID = D1.OrderID}" & _
" AS chapProducts" & _
" RELATE CustomerID TO CustomerID)", _
"Provider=MSDataShape;" & _
"Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\NWIND.mdb"
rs.Save "C:\rs.xml", adPersistXML
End Sub

Care to speculate how many lines of code would be required to achieve
the same using DAO in place of ADO?

Now, you may say that you do not need (nor would want) to save a
hierarchical recordset as XML but bear in mind you are telling other
people that *they* do not need ADO.

Jamie.

--