Link different _be database

[poliv]

I have a database (mde) linked to a _be (mdb) database, and I want to now if it is possible (and how) to change the _be (mdb) database to other _be (mdb).
Example:
Inventory.mde is the main database and I want to store all the data from the year 1999 in a 1999_be.mdb database, and from the year 2000 in a 2000_be.mdb database.
When I open the Inventory.mde database I want to have an option to choose between 1999_be.mdb or 2000_be.mdb.

Thanks

 

[PaulF]

Yes, you need a form, a module with a Public Variable, and a Public Function, and finally a macro named AutoExec. The AutoExec macro will run as soon as the MDB opens.

'In a module add the following.

Public strYear As String
'------------------------------------------------------------
' This function will open a form to ask the user to select
' A year from a ComboBox, then after the year is selected
' in the ComboBox, the value is stored to a Public Variable
' and then the form closes using a DoCmd.Close in the
' AfterUpdate Event for the ComboBox. It first checks to
' ensure that there is a value in the Public Variable and
' then it deletes the table which is already linked, and
' passes a command to link to the appropriate mdb.
'
' If no value exists in the Variable, a message box appears
' to explain the problem, and then then Access closes.
'
' You need to have both databases in the same directory, and
' the only differences in the name of the databases is the year.
' The tables need to have the same name, otherwise additional
' coding will be required to test for the available options.
' frmGetYear is the name of the form, and strYear is the Public
' Variable used to capture the value in the ComboBox on the form,
' And Table1 is the name of the Table in the databases to be Linked,
' and is also the name of the table is this database.
'------------------------------------------------------------
Function SetUpTableLink()
On Error GoTo SetUpTableLink_Err
DoCmd.OpenForm "frmGetYear", , , , , acDialog
If strYear = "" Or strYear = " " Then
MsgBox "No Year Selected for Data to view" & vbCrLf & "Unable to display data,Exiting Program", vbCritical, "No Data For Viewing"
DoCmd.Quit
Else
DoCmd.DeleteObject acTable, "Table1"
' if 2000 is selected in the ComboBox then this will link to Table1 in D:\2000_be.mdb
DoCmd.TransferDatabase acLink, "Microsoft Access", "d:\" & strYear & "_be.mdb", acTable, "table1", "table1", False
End If
SetUpTableLink_Exit:
Exit Function

SetUpTableLink_Err:
MsgBox Err.Number & " : " & Err.Description, vbCritical, "Unexpected Error Occured in Function SetUpTableLink"
Resume SetUpTableLink_Exit

End Function


' Create a new form (frmGetYear), with one ComboBox
'(cboYear), Add this code to the AfterUpdate Event
' for the ComboBox:

Private Sub cboYear_AfterUpdate()
strYear = cboYear
DoCmd.Close acForm, "frmGetYear"
End Sub

' Create a Macro named AutoExec and add one action.
RunCode
' and for the Function Name, add
SetUpTableLink ()

PaulF