Switch backend button

[Sierras]

I have a split database with a frontend and backend.
Users would like to archive the current data that is in the backend and
start fresh for a new year. But would like to be able to click a button
to view the old data and switch back again. (Or perhaps a combo box with
all the previous backend years to pick from). Also, the top form should
have a field that shows which back-end they are looking at.

Is there an easy way of doing this?

Thanks...

 

[Jeff Boyce]

Unless your user has 100's of Mbytes of data, you (and the user) are better
off NOT putting some data in db1, some in db2, some in db3, ... and then
trying to connect them all together.

Instead, include either a Yes/No field (you could call it, say, "Archived")
in the table(?s?) that hold the data being considered. Then modify the
everyday queries to exclude data that has Archived = Yes. You can still
create a query to look at any of the historical data.

Or include a date/time field that holds the date/time when the record was
archived, and modify your queries to exclude any before, say, 1/1/2007 (or
any other time period).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Arvin Meyer [MVP]]

Try running code like this:

Public Function ChangeLinks(strNewPath As String) As Boolean
'************************************************************
' Name: ChangeLinks
' Purpose:
'
' Inputs: strNewPath As String
'
' Returns: Boolean
'
' Author: Arvin Meyer
' Date: September 5, 1998
' Comment: Change the Link to a different back-end database
'
'*************************************************************
On Error GoTo Err_ChangeLinks

Dim db As DAO.Database
Dim i As Integer

Set db = CurrentDb

For i = 0 To db.TableDefs.Count - 1
If Len(db.TableDefs(i).Connect) > 0 Then
db.TableDefs(i).Connect = ";DATABASE=" & strNewPath
db.TableDefs(i).RefreshLink
End If
Next i

db.TableDefs.Refresh
ChangeLinks = True

Exit_ChangeLinks:
Set db = Nothing
Exit Function


Err_ChangeLinks:
ChangeLinks = False

Select Case Err

Case 0

Case 3044
Resume Next

Case Else
MsgBox Err.Number & ": " & Err.Description
Resume Exit_ChangeLinks
End Select

End Function

 

[Sierras]

WOW - this is just what I was looking for.

Thanks Arvin. I'll give it a try.

Also, I too tend to agree with Jeff's suggestion and have discussed
this with the users as well, but I am not a user of this database. I'm
just trying to give them what they want.

Thanks again...

 

[Jeff Boyce]

An observation -- users can be emphatically explicit about what they want...
and exactly and precisely wrong! Part of my job is to explain the
consequences of their insistances, and to offer possible alternatives, where
germane.

If they still want to drive nails with a chainsaw after I point out
consequences and alternatives, I step back ... far!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Arvin Meyer [MVP]]

Also, I too tend to agree with Jeff's suggestion and have discussed
this with the users as well, but I am not a user of this database. I'm
just trying to give them what they want.

I also agree. Sometimes however, changing backends is necessary. The 2
instances when I needed to (and wrote the code for) were a connection to 6
different database back-ends created by another "developer" to attach to a
3rd party system. I think, by the 4th or 5th one of these he started to get
the idea that he'd designed incorrectly. He quit after the 6th one and I had
to clean up the mess. Writing that piece of code was much faster (and
therefore much cheaper as a quick fix) than redesigning and moving the data
which I eventually did. Less than an hour's work took the pressure off and
allowed me to rebuild the system correctly.

The other instance was legitimate. The database consisted of hundreds of
thousands of records which where accessed only a few times a year and about
80,000 which were accessed daily. I archived the old data to speed up the
performance and lighten the load on the network. When the users need the
data, it was only a click away.

 

[Sierras]

Well I tried this and am having a hard time getting it to work.
I put the function in a module and called the module basChangeLinks
Then created a button on a form to call the funtion

Call basChangeLinks.ChangeLinks

But Access gives me a compile error: Argument not optional.

How can I get this funtion to work?

Thanks...

 

[Arvin Meyer [MVP]]

You need to add a path for:

strNewPath

in the body of the function:

strNewPath = "C:\FolderName\FileName.mdb"

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Well I tried this and am having a hard time getting it to work.
I put the function in a module and called the module basChangeLinks
Then created a button on a form to call the funtion

Call basChangeLinks.ChangeLinks

But Access gives me a compile error: Argument not optional.

How can I get this funtion to work?

Thanks...

 

[Sierras]

OK - but if I hard code it into the body of the function, and put it
into a module, how would I call it to go back to the original
back-end. Or would I create a button and assign this function to it's
on-click command to go to one back-end and another button to go back.
And lastly, what if I users have the back end in different folders?

Thanks for your help...

 

[Arvin Meyer [MVP]]

You'll note I wrote that function almost 9 years ago. There will need to be
some minor alterations to get it to work for you.

First, Dim strPath instead of using it as an argument of the function. Then
use a Select Case statement with a message box, or perhaps a toggle button.
If you need more than 2 choices, use an Option Group Frame or even a list
box with the Case statement.

 

[Guest]

Put the names of the various back ends into a user ("superuser") table
perhaps called "tblDatabaseNames":

dbName Descripton
---------- -----------------------
C:\blah Regular Database
C:\blah2 Last Years' Data
C:\bhah3 1986 data

Then give them a combo box choice of databases with a recordsource tied to
that table, and you use the dbName field as the calling parameter for Arvin's
function.

 

[jschaefer]

Just came across this thread which is over 4 years old. I need to be able to do the same thing as Sierras which is switch backends as the client adds several thousand records every day so we are forced to archive yearly.

I tried compiling the code that Arvin wrote with strNewPath = "C:\FolderName\FileName.mdb" in the body of the function and Dim strNewPath but I am getting the same error that Sierras was receiving, "Argument not optional."

Was there any other modifications that needed to be made to make the code work?

Thanks for your help!