AOD or DAO connection - which is most suitable and help making connection 2

[Moss100]

I have a front end database called ACCOUNTS.

I have a back end database called MINI

The MINI database contains several thousand tables. For this example I would like to use table 1250.

Thus I would like to establish the best way to bring the data from table 1250 in the backend into a form in my front end ACCOUNTS database.

I do not want to create a linked table and so have been looking at DAO and AOD connections.

The main requirements are as follows:

1. That the rear end database is not locked up by accessing data for the front end form
2. That the data sent is read only and can not be manipluated by the user of the front end.

(Does a snapshot recordset match the above requirements?)

I have been trying to solve this for several days, but get confused by the differnet options.

I would very much apprecaite help.


The code I have cobbled togther so far works on the on open event, but fails on the on close event (i don't know why).

Is the code any good overall and is there a better way????


Sincere thanks Mark


Private Sub Form_Open(Cancel As Integer)


Dim wrkJet As Workspace
Dim dbsmini As DAO.Database
Dim rstmini As DAO.Recordset

'Create Microsoft Jet Workspace object.
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

'Open Database object from saved Microsoft Jet database
Set dbsmini = wrkJet.OpenDatabase("C:\mini.mdb", False, True)
Set rstmini = dbsmini.OpenRecordset("1250", dbOpenSnapshot)

Set Me.Recordset = rstmini

Me![Txtdate].ControlSource = "Date"

End Sub

Private Sub Form_Close()

wrkJet.Close
rstmini.Close
Set rstmini = Nothing

End Sub

 

[lameid]

The difference in DAO and ADO is largely performance.

Generally,

For Access data use DAO.
For any other data use ADO.

Beyond that, you might want to use unbound forms and use code to populate the forms.

Although bound forms are much easierer, if you can use them.

 

[PHV]

Why open a workspace ?
I'd try this:

Me.RecordSource = "SELECT * FROM [1250] IN 'C:\mini.mdb'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Moss100]

Thanks - if I try:

Me.RecordSource = "SELECT * FROM [1250] IN 'C:\mini.mdb'"


It brings the data in fine

Instead of using [1250] as the table name, I have the table name stored in a text box called [TABLEREF] - how would I use this instead.

I have tried:

Me.RecordSource = "SELECT * FROM [TABLEREF] IN 'C:\mini.mdb'"

But it errors.


Thanks mark

 

[PHV]

Me.RecordSource = "SELECT * FROM [" & Forms![name of form]!TABLEREF & "] IN 'C:\mini.mdb'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Moss100]

OK I see this works -

Set rstGemini = _
dbsGemini.OpenRecordset("SELECT * " & _
"FROM " & [Txttable] & " ORDER BY date desc", _
dbOpenSnapshot)

 

[PHV]

Again, why using a Workspace and a Database and a Recordset ???
If you want to be sure that the table is read-only, then use a not updatable query like this:
Me.RecordSource = "SELECT DISTINCT * FROM [" & [Txttable] & "] IN 'C:\mini.mdb' ORDER BY [Date] DESC"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Moss100]

Thank you so much to all who helped me - my final code is below - hopefully it may help someone else - thanks again all!!!


Option Compare Database

Dim wrkJet As Workspace
Dim dbsmini As DAO.Database, rstmini As DAO.Recordset

Private Sub BTN_LOAD_ACCOUNT_DblClick(Cancel As Integer)
'Create Microsoft Jet Workspace object.
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

'Open Database object from saved Microsoft Jet database for exclusive?? use
Set dbsmini = wrkJet.OpenDatabase("C:\temp\mini.mdb", , False)

On Error Resume Next


Set rstmini = _
dbsmini.OpenRecordset("SELECT * " & _
"FROM " & Me.[Txttable] & " ORDER BY date desc", _
dbOpenSnapshot)

If err > 0 Then
MsgBox "Can not open Account"
Else

Set Me.Recordset = rstmini

Me![Txtdate].ControlSource = "Date"
Me![TxtTYPE].ControlSource = "Type"
Me![TxtAmountIN].ControlSource = "Amount In"
Me![TxtAmountOUT].ControlSource = "Amount Out"

End If

On Error GoTo 0
End Sub
----
Private Sub Form_Close()

On Error Resume Next
rstmini.Close
Set rstmini = Nothing
Set dbsmini = Nothing

End Sub