Back End Databases 1

[rookery]

Is it possible for the user to choose which back-end database s/he wants the information they're entering, to be stored in?

For example, lets say I had a simple data-entry Form with an option box with 2 choices on it. The user would enter all the details then choose via the option box, which database to save it too.

This would be useful when you have two databases that contain the same table structures etc.

Any ideas?

 

[SgtJarrow]

If, as you say the two table structures are the same, then, yes...you can do this. SImply create the form with unbound fields, and include the radio button selector/combobox/whatever to determine which database to save it to. Then, when the user clicks the save button (or whatever), you will evaluate which databse to say to, open a connection to that database, create a recordset connection to the table and add the record in.

As an aside (an to get you thinking), can I ask why you would have seperate databases??? Ideally in this type of setup, I would create one database with the table....and add one additonal field to this table to place your "database" identifier instead of separate databases. It's not important that someone else can do in one step what it took you ten to do...the important thing is that you found a solution.

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[rookery]

Thanks for your reply Rob. You say "..open a connection to a database.." but it is this that I dont know how to do. Is it something to do with TableDefs and Collections etc? I'm desperately trying to read up on this subject as we speak!

I take your point on the use of an "identifier" field within the same table and to be honest as I'm doing this for someone else, I'm not sure why they didnt go down that route!

 

[SgtJarrow]

Okay rookery.....here is you code.....

place this in a module and save it. After doing your check to see which database you want to save, merely assign the variables during the call as necessary. You will also nee to look at comments in the code to finish it off to your specifications...

the call is as such...

Connect2Database("C:\My Documents\test.mdb", "tblTest&quot

Let me know if you need anymore help.


'********* Start Code ***************
Option Compare Database
Option Explicit

'Define the API calls
Private Declare Function apiSetForegroundWindow Lib "user32" _
Alias "SetForegroundWindow" _
(ByVal hwnd As Long) _
As Long

Private Declare Function apiShowWindow Lib "user32" _
Alias "ShowWindow" _
(ByVal hwnd As Long, _
ByVal nCmdShow As Long) _
As Long

Private Const SW_MAXIMIZE = 3
Private Const SW_NORMAL = 1

Public Function Connect2Database(strMDB As String, strTable As String) As Boolean
'strMDB is the full path to the database, strTable is the table name in the database

On Error GoTo ErrorHandler

Dim objAccess As Access.Application
Dim lngRet As Long
Dim db As Database
Dim rs As Recordset

If Len(Dir(strMDB)) > 0 Then
Set objAccess = New Access.Application
With objAccess
lngRet = apiSetForegroundWindow(.hWndAccessApp)
lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
'the first call to ShowWindow doesn't seem to do anything
lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
.OpenCurrentDatabase strMDB
Set db = objAccess.CurrentDb
Set rs = db.OpenRecordset(strTable, dbOpenDynaset)
rs.AddNew
'this section: the first field in the table is index 0 or you can use the names...
' rs.fields(0), rs.Fields(1) or rs.Fields("Name&quot, rs.Fields("Street&quot
' add as many a you need prior to the rs.Update
rs.Fields(0) = "Bird"
rs.Update
End With
End If
testme = True

ErrorExit:
objAccess.Quit
Set objAccess = Nothing
Set rs = Nothing
Set db = Nothing
Exit Function

ErrorHandler:
testme = False
Resume ErrorExit

End Function
'************* End Code *************** It's not important that someone else can do in one step what it took you ten to do...the important thing is that you found a solution.

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[rookery]

Wow! Thanks for your reply but I found that simply using these 2 lines of code did exactly what I wanted it too:

Set dbs = DBEngine(0).OpenDatabase("C:\Windows\Example.mdb&quot
Set MyRs = dbs.OpenRecordset("DemoTable",dbOpenDynaset)

I can put in an option box and an If...Then statement and away it goes!

Cheers.