Dynamic Forms

[Mark D'Agosta]

Using Access 2002.

My primary database contains code that creates new databases on the fly (one
for each new client, for example). The new databases all contain an
identical table. I would like to create a form, in my primary database,
that will display any client's data. For example, I dynamically create a
new database (ex: NewClient.mdb), dynamically create a table
(NewClientJobsTable) in that new database. I then populate the new table
with data (client jobs; large table; could be tens of thousands of rows).
Then, I'd like to be able to query the data in the new table, filtering on
Job Id, but display it in a form that was defined in the primary database.

I don't know how to create an unbound form in one database, and then
dynamically bind it to another database at run time. Is this even the right
approach? Can I also dynamically define a Form in the new databases, and
then bind that form to the table? Anyone have any ideas on the best way to
accomplish this task?

The idea of each client having its own database is an absolute requirement
(please don't make me go through the whole story... I fought against the
idea, and lost). Other than that, I'm wide open to any ideas!

Thanks,

Mark D.

 

[Steve Huff]

If I understand your problem correctly I think the best solution (and I've
had to do this before) is to dynamically create a linked table to the table
in the other database. Since the table name is always the same you just
need to know the location of the customer database to create the link,
assuming you have some way of knowing that you would do the following:

1) Have a form open up that prompts for the customer then do a look up on
some table that has the location of the customer's database say
"c:\cust\123.mdb"

2) call the function I put in here LinkTable with that info.. i.e. ->
LinkTable "c:\cust\123.mdb", "tblData", True

3) have the form open up already linked to the table which I called tblData
(to create that form it's simplier if you already have a link create to some
random customer file with that table which I'm calling tblData

'=======start code
Public Function LinkTable(strDB As String, strTBL As String, bolOverWrite As
Boolean) As Boolean
'//======================
'// Creates a link in CurrentDB to table strTBL in strDB
'// If bolOverWrite = TRUE then strTBL will be over written
'// if it already exists.
'// Returns TRUE if successul, FALSE otherwise.
'//=======================
'// Written By: Steve Huff
'// Date: 1/11/04

On Error GoTo ErrHandle

Dim db As Database
Dim tdf As TableDef

Set db = CurrentDb

If TableExists("", strTBL) And bolOverWrite = True Then
db.TableDefs.Delete strTBL
End If

Set tdf = db.CreateTableDef(strTBL)

tdf.Connect = ";DATABASE=" & strDB
tdf.SourceTableName = strTBL
db.TableDefs.Append tdf

LinkTable = True
Exit_Function:
Set tdf = Nothing
Set db = Nothing
Exit Function
ErrHandle:
LinkTable = False
Resume Exit_Function
End Function
'=======end code

 

[Mark D'Agosta]

Steve,

That sounds like an awesome solution. The only area that I'm still unclear
about is your step #3. (I should have said right up front that I'm not an
Access developer. I'm more comfortable with VB and SQL Server. So forgive
my ignorance about the mysterious workings of Access forms).

OK, so let's say that I dynamically create the database and the table. Then
I use your LinkTable function to create the linked table in the primary
database. What is the process for that last step of linking the form to the
new linked table? The form will be created linked to a table in a dummy
client database file, as you suggested. But what is the process for
changing the form to look at the new linked table?

Appreciate your time and your help,

Mark D.

 

[Steve Huff]

Ok say the table name in all the customer tables is called tblData. Go
ahead and create a link to one of them (assuming you know how to create a
link by hand if not let me know) then go into the Customer form and bound it
to tblData (or whatever the real table name is). Now before you open that
Customer Form you are going to call the LinkTable fucntion below which is
going to delete the link to tblData and recreate it to whatever back end
database you tell it. Therefore when you re-open your Customer form it will
be looking at tblData still HOWEVER tblData will be looking at a different
backend database.

Let me know if that helps or if you are still confused.

-Steve Huff

 

[Mark D'Agosta]

Steve,

Understood. From the form's perspective, nothing has changed.

Nice solution, Steve. Thanks.

Mark

 

[Tom Wickerath]

Hi Steve,

Please include a copy of the TableExists procedure referenced in your code sample.


Tom
_________________________________________



If I understand your problem correctly I think the best solution (and I've
had to do this before) is to dynamically create a linked table to the table
in the other database. Since the table name is always the same you just
need to know the location of the customer database to create the link,
assuming you have some way of knowing that you would do the following:

1) Have a form open up that prompts for the customer then do a look up on
some table that has the location of the customer's database say
"c:\cust\123.mdb"

2) call the function I put in here LinkTable with that info.. i.e. ->
LinkTable "c:\cust\123.mdb", "tblData", True

3) have the form open up already linked to the table which I called tblData
(to create that form it's simplier if you already have a link create to some
random customer file with that table which I'm calling tblData

'=======start code
Public Function LinkTable(strDB As String, strTBL As String, bolOverWrite As
Boolean) As Boolean
'//======================
'// Creates a link in CurrentDB to table strTBL in strDB
'// If bolOverWrite = TRUE then strTBL will be over written
'// if it already exists.
'// Returns TRUE if successul, FALSE otherwise.
'//=======================
'// Written By: Steve Huff
'// Date: 1/11/04

On Error GoTo ErrHandle

Dim db As Database
Dim tdf As TableDef

Set db = CurrentDb

If TableExists("", strTBL) And bolOverWrite = True Then
db.TableDefs.Delete strTBL
End If

Set tdf = db.CreateTableDef(strTBL)

tdf.Connect = ";DATABASE=" & strDB
tdf.SourceTableName = strTBL
db.TableDefs.Append tdf

LinkTable = True
Exit_Function:
Set tdf = Nothing
Set db = Nothing
Exit Function
ErrHandle:
LinkTable = False
Resume Exit_Function
End Function
'=======end code

 

[Steve Huff]

If you are using Access 2000 and up; then there is one last thing to
consider, I'm using DAO in that code not ADO; so you will have to add the
DAO reference if you have not already done so and if you are using ADO you
will need to specficy DAO in the declarations on the vars like this:

Dim db As DAO.Database
Dim tdf As DAO.TableDef

I still use DAO in all my Access databases, I'm not convienced of any
benefits ADO gives you unless you plan on upsizing to SQL SERVER.

-Steve Huff

 

[Albert D. Kallal]

You got a good answer here already.

As a side note, the idea, or concept of splitting your application into two
parts is a recommended approach.

in other words, you have the program part (forms, code, queries, reports
etc). and then you connect this to the data part. That data part can be sql
server, or in your case, simply another mdb file. This also facilitates
program updates, as you can add new forms, new reports code etc..but not
touch the data part. Hence, a general development process in ms-access is to
use what is called a split database (there is a data splitter in the
tools->database utilizes menu.

You can read up on splitting here:

http://www.granite.ab.ca/access/splitapp.htm

 

[Steve Huff]

Opps, sorry about that:

'==========================start code
Function TableExists(strDatabase As String, strTable As String) As Boolean
On Error GoTo TableExists_Error
'//Written By: Steve Huff
'//Written: 7/18/02

' Comments : Determines if the named table exists in the named database
' Parameters: strDatabase - path and name of the database to look in or ""
(blank string) for the current database
' strTable - name of the table to check
' Returns : True - table exists, False - table does not exist

Dim db As Database
Dim intTableCount As Integer, x As Integer

If IsBlank(strDatabase) Then
Set db = CurrentDb()
Else
Set db = OpenDatabase(strDatabase)
End If

intTableCount = db.TableDefs.Count

TableExists = False '//Default to table not found

For x = 0 To intTableCount - 1
If db.TableDefs(x).Name = strTable Then
TableExists = True
Exit For '//If table is found, get out of loop and return True
End If
Next x

TableExists_Exit:
Set db = Nothing
Exit Function

TableExists_Error:
Select Case Err.Number
Case Else
MsgBox "Error #: " & Err.Number & vbCrLf & "Error Description: "
& Err.Description, vbCritical, "Error occured in TableExists"
Resume TableExists_Exit
End Select
End Function
'==========================end code

 

[Tom Wickerath]

Thanks Steve.

Tom

_____________________________________



Opps, sorry about that:

'==========================start code
Function TableExists(strDatabase As String, strTable As String) As Boolean
On Error GoTo TableExists_Error
'//Written By: Steve Huff
'//Written: 7/18/02

' Comments : Determines if the named table exists in the named database
' Parameters: strDatabase - path and name of the database to look in or ""
(blank string) for the current database
' strTable - name of the table to check
' Returns : True - table exists, False - table does not exist

Dim db As Database
Dim intTableCount As Integer, x As Integer

If IsBlank(strDatabase) Then
Set db = CurrentDb()
Else
Set db = OpenDatabase(strDatabase)
End If

intTableCount = db.TableDefs.Count

TableExists = False '//Default to table not found

For x = 0 To intTableCount - 1
If db.TableDefs(x).Name = strTable Then
TableExists = True
Exit For '//If table is found, get out of loop and return True
End If
Next x

TableExists_Exit:
Set db = Nothing
Exit Function

TableExists_Error:
Select Case Err.Number
Case Else
MsgBox "Error #: " & Err.Number & vbCrLf & "Error Description: "
& Err.Description, vbCritical, "Error occured in TableExists"
Resume TableExists_Exit
End Select
End Function
'==========================end code

 

[Tom Wickerath]

Looks like we need a copy of your IsBlank procedure, referenced by the TableExists function.....


Tom

 

[Steve Huff]

Sorry about that AGAIN... in the future I'll try to look over my code some
more before posting it to make sure I give you any supported functions....

'====================start code
Public Function IsBlank(anyValue As Variant) As Boolean

'Returns true if a value is Null or an empty string
IsBlank = False
If IsNull(anyValue) Then IsBlank = True Else If Trim(anyValue) = "" Then
IsBlank = True

End Function
'=========================end code

--Steve Huff

 

[Tom Wickerath]

Thanks Steve.

I got it to compile correctly this time, and it works pretty nice as long as the remote database
does not have a password. I suppose that wouldn't be too hard to add as an optional parameter. My
only suggestion might be to add a RefreshDatabaseWindow statement to your LinkTable function,
just after the line of code that reads: LinkTable = True

LinkTable = True

RefreshDatabaseWindow

Exit_Function:

This way, a user will immediately see the table if they happen to have focus set to Tables in the
database window. Also, if the bolOverWrite value passed in was True, but an existing table link
failed to update for any reason (such as a password) then the link gets deleted, but the window
may not immediately be refreshed. Otherwise, a user will need to switch to any other object view,
and then back to Tables, in order to see the newly linked table. I think (not positive) that
RefreshDatabaseWindow is available for Access 2000 and above. Of course, if you are not exposing
the database window to the users, then this is a moot point.

Tom
___________________________________


Sorry about that AGAIN... in the future I'll try to look over my code some
more before posting it to make sure I give you any supported functions....

'====================start code
Public Function IsBlank(anyValue As Variant) As Boolean

'Returns true if a value is Null or an empty string
IsBlank = False
If IsNull(anyValue) Then IsBlank = True Else If Trim(anyValue) = "" Then
IsBlank = True

End Function
'=========================end code

--Steve Huff