Form recordsource as external table 2

[Moss100]

Hello,

I have two databases.

One is an oldish purchased database. It holds accounts information in around 2000 separate tables. For ease i will call it ACCOUNTS.

The other is my database. Called CLIENTS. It has a table called PERSONS which in turn has a field CLIENTACCOUNT which stores the corresponding table number for the ACCOUNTS database.

I would like to open a form in CLIENTS called INFO which will display the correct table from accounts.

Please could someone help with the best way to connect to the external database.

I have linked tables in the past, but obviously linking 2000 + tables manually is a none starter.

My main criteria for this project is as follows.

1. The data is for display only and is not to be updated.
2. I need to link to the ACCOUNTS database whilst it is open and in use and want to minimise any chance of the ACCOUNTS database being corrupted by others accessing it. Is it best to access he data as a snapshot from this point if view?


Many thanks, mark.

 

[dhookom]

Do you actually need links to all 2000 separate tables all the time? Can you swap links in as needed?

Why 2000 separate tables? Are they all the same structure?

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Hi, no I only need the link when I open the form to get the data.

All the tables have the same structure.

Thanks.

 

[Moss100]

To be clear I only need to link to one table at a time when I open the form

Thanks Mark

 

[dhookom]

How are the tables named?

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

They are numerically named 1, 2, 3 etc..

I have the names storred in my database, so it is just the matter me establishing the best way to link to an external Access database table.


Thanks

 

[dhookom]

I would create a query with the Source Database set to the path to your ACCOUNTS database file name. You can use SQL like

SELECT *
FROM tblOne;

Then use some code like faq701-7433 to change the SQL to something like:

SELECT *
FROM tblTwo;

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

I will try to clarify what I am trying to do here....

From a continuous form in my clients database I will select a record, The record will hold the table name for the external accounts database. i.e. Mr Smith 1354

On selecting the record I want to open a form which will display data from the external table 1354.

I have cobbled togther the following:

Function Get_Account()
On Error GoTo Get_Account_Err

DoCmd.TransferDatabase acLink, "Microsoft Access", "c:\accounts.mdb", acTable, "1354", "1354", False


Get_Account_Exit:
Exit Function

Get_Account_Err:
MsgBox "Sorry, the Account can not be found."
Resume Get_Account_Exit

End Function


The above works but the following concerns me:

1. If the function is run several times it creates a new instance of linked table each time i.e. 1354, 13541, 13542 etc...
I thought that I could close the linked table each time a user closes the form, but if two users are accessing it then closing it when another user is accessing it may cause problems.

Is there a neater way of doing this? I guess really I just want to grab the static data from the applicable external table and display it, and would like avoid linking tables etc. I am also very keen to prevent corruption of the external ACCOUNTS database.

Please forgive my limited coding abilty.

Many thanks Mark

 

[dhookom]

I think my solution is much simpler. Every user should have their own copy of the front end Access file.

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Thank you Duane,

I have read up a little more and am now staring to see that your solution is probably best as it will avoid lots of linked tables.

Do I understand correctly that I need to do the following.

1. Use the forms on open event to fire a DAO connection to the back end database table, get this to return a recordset as a snapshot and then set this recordset as the forms record source.
2. Use the on close event to delete the record set.


By doing the above will I prevent any changes / corruptions of the back end database tables and also if the back end database is being updated at the same time will this affect the event?

I would greatly appreciate if someone could help me with the code for this as my code skills are pretty poor as I have always used macros.


Many thanks mark.

 

[dhookom]

I didn't mention anything about DAO or recordsets. I simply suggested creating "a query with the Source Database set to the path to your ACCOUNTS database file name". Then use some code to change the SQL property of the query as needed.

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Ok to try and understand matters, I have created a database on the root of my c drive called MINI

MINI contains a table 1250

My front end form has he following code:

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, shared use, read only
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



To my joy the form loads the external data OK, but when I close the form i get error: 'runtine error 424, object required' which highlights wrkJet.Close

Can anyone advise me as to what causes the on close error and in addition any ways to improve the code (i'm just learning code)

To reiterate, I just want the user of the front end form to have read only access and I am keen to protect the data in the back end table.

Many thanks Mark

 

[dhookom]

Did you even consider or try my suggestion?

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Hi hook'd -

I am trying my best to follow your suggestion - but unfortnatley as my coding is so poor it if difficult for me to understand how best to link to external data from it. I can not follow the linked code.

I understand -

SELECT *
FROM tblOne;

but this doesnt really get me to external table.

I guess it is very easy when you know how, but.....

Regards Mark

 

[dhookom]

Create a new, blank query and view the query properties. There is a query property "Source Database". This is where you can enter the full path and name of your Access database containing the tables. View the SQL view and set the SQL statement to something like:

SELECT *
FROM tblYourTable

You can then use the code I suggest to change the SQL property of your new, saved query.

Duane
Hook'D on Access
MS Access MVP

 

[sxschech]

For the sql statement, you would use the IN clause.

SELECT *
FROM tbl1250 in 'C:\mini.mdb'

For the query replacement (code is like what Dhookum provided, but not as a function)

Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim strSQL As String
    DIM oldtblno as string
    DIM newtblno as string

    Set db = CurrentDb
 'change table name to the table number
        Set qd = db.QueryDefs("qryExternalTableNumber")
        qd.SQL = Replace(qd.SQL, oldtblno, newtblno)

Since not sure how you are selecting the new table no, would need some more information to finish the code for oldtblno and newtblno. Is the table number picked by the user on a form? Are the table names having the same length or naming convention? tblOne (6 chars); tblTwo (6 chars), etc?

 

[dhookom]

sxschech's solution is basically the same as PHV suggested in another thread. It's similar to mine and perhaps even simpler to implement. The saved query "Recordset Type" can be set to Snapshot so it is read-only.

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Thanks for your help -

Today I got working the following code:


Option Compare Database

Dim wrkJet As Workspace
Dim dbsmini As DAO.Database, rstmini As DAO.Recordset
Private Sub Form_Open(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)

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

All is working OK, but instead of using table 1250, I want to set the table number from a textbox called "TABLEREF"

If I replace the line:

"FROM 1250 ORDER BY date desc", _

with

"FROM [TABLEREF] ORDER BY date desc", _

I get an error - what do I need to do?

Thanks Mark

 

[dhookom]

Me.RecordSource = "SELECT DISTINCT * FROM [" & Me.[TABLEREF] & "] IN 'C:\mini.mdb' ORDER BY [Date] DESC"

Replace the C:\Mini.mdb with your Access file name.

Duane
Hook'D on Access
MS Access MVP

 

[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