Two databases in one project ...

[Margaret]

Hi,

I have two MS Access databases (Sales2006.mdb & Sales2007.mdb) and two Checkboxes (CheckSales2006 & CheckSales2007).

What I need is, when I Select CheckSales2006 I should get the data from Sales2006.mdb and when I Select CheckSales2007 I should get the data from Sales2007.mdb.

I have the code in a module like the following:

Public Sub data_connect()
Dim path As String
path = App.path & "\[Highlight=VB]Database2006.mdb

"
db.CursorLocation = adUseClient
db.Open "Provider=microsoft.jet.oledb.4.0;persist security info=false;data source=" & path & ";"
End Sub[/Highlight]

How to modify the code accordingly?

Regards,

Margaret

[ganeshmoorthy]

Is the option permanent for one full session or will it change in between...

[Margaret]

Is the option permanent for one full session or will it change in between...

Yes, it need to be changed in between.

Margaret

[Hack]

Can both be open at the same time?

If not, then I would suggest using option buttons rather than checkboxes.

There are a couple of things that come to mind.

The first is to use a command button (perhaps called cmdConnect) which evaluates which checkbox/option button is selected, and issues the appropriate connect string for that database.

Another, and one that I'm not overly fond of, but is nonetheless and possibility, is to place the appropriate connect string in the click event of the checkbox/option button so that the connection is made the instant you select the control. Again, I'm not wild about this as it is possible the user could select the wrong one by mistake.

[si_the_geek]

I would say that there should only be one database file.

In the appropriate table(s), simply add an extra field that contains the date/year (if you dont have one already), and use a Where clause to limit the data that is displayed in each case.

This wont slow things down noticeably, and will be less prone to problems. Switching connections while recordsets are open is guaranteed to lead to issues - most likely data corruption and/or program errors.

[Margaret]

Can both be open at the same time?

If not, then I would suggest using option buttons rather than checkboxes.

There are a couple of things that come to mind.

The first is to use a command button (perhaps called cmdConnect) which evaluates which checkbox/option button is selected, and issues the appropriate connect string for that database.

Thanks. Then, I would prefer command button using option buttons. Please help me with the code.

Margaret.

[Hack]

You already know the code.

You have two databases.

You will need two connection strings, one for each database.

In the connect button, just put

Private Sub cmdConnect_Click()
If Option1.Value = True Then
   'run code to connect to 2006 database
Else
   'run code to connect to 2007 database
End If

You didn't answer my question about whether the two databases can be open at the same time. If they can not, then include code to disconnect before you run the code to connect.

[Margaret]

You didn't answer my question about whether the two databases can be open at the same time. If they can not, then include code to disconnect before you run the code to connect.

Now I use only Database2006, but in January I will use Database2007 and freqently I need to get the details from Database2006 as well.

Margaret

[si_the_geek]

So what is the reason for two separate databases, rather than just one as I described above?

(are you planning to re-write this app every year to work with the new database files?)

[Hack]

So what is the reason for two separate databases, rather than just one as I described above?

(are you planning to re-write this app every year to work with the new database files?)

Excellent questions.

With each passing year does the prior year database become an archive database?

How many records, typically, will your database have after one calendar year?

[Margaret]

Excellent questions.

With each passing year does the prior year database become an archive database?

How many records, typically, will your database have after one calendar year?

Since I am a learner I can't create any complex projects. That is why I will create a new database with name Database2007 and copy all the table structures of Database2006 to it. So that it will be easy for me and no need to create a new project for that.

Margaret

[Hack]

Ok, but going forward, which is where I believe si_the_geek was headed, what will happen in December of 2007....will you be creating a new 2008 database and rewriting your project to accommodate that?

[si_the_geek]

Indeed.. and the suggestion I made will mean you only need to change a few lines of code - which is less work than you are currently doing to create/use the new database!

We understand that you may not be able to do it all on your own yet, but we can help you.

[Margaret]

Indeed.. and the suggestion I made will mean you only need to change a few lines of code - which is less work than you are currently doing to create/use the new database!

We understand that you may not be able to do it all on your own yet, but we can help you.

Hi, Thanks a lot for your kind suggestion and for the usual help.

Margaret