Scanning for new tables in a Back End (BE) database

[Ben]

Hi all,

Hope you had a nice Thanks Giving. I want to ask is there any way to scan a
back end database for any new tables added? Currently we have a Access 2003
back end sitting on a server. The Front End (FE) is distributed to various
users.

Sometimes, a user through the FE could add a new table to the back end. But
because the tables in the FE are merely links to the BE, when a user creates
a table to the back end, other users with their own copy of the FE, will not
have automatically created a link to the new BE table.

Is there a way to build some code logic into the FE when the FE loads, such
that, each time it loads, it will scan the BE tables to see if there are any
new table that the FE does not have a link to and if so, create a new link in
the FE to the new table in the BE? Can you share some code snippet?


Thanks,

Ben
--

 

[Douglas J. Steele]

Dim dbBackend As DAO.Database
Dim tdfBackend As DAO.TableDef

Set dbBackend = OpenDatabase("F:\Folder\File.mdb")
For Each tdfBackend In dbBackend.TableDefs
If (tdfBackend.Attrbutes And dbSystemObject) = 0 Then
' tdfBackend is a non-system table.
' Check whether you already know about it...
End If
Next tdfBackend

dbBackend.Close
Set dbBackend = Nothing


No offense, but in my opinion something's wrong if your users are adding
tables to the backend of a shared database!

 

[David H]

Sometimes, a user through the FE could add a new table to the back end.

As Mr. Steele stated, there are concerns with this, however my questions is
HOW do the users a table to the backend using the frontend? Are you referring
to users linking to existing tables that they weren't previously linked to or
to users actauly creating a new table.

 

[Ben]

--

Dim dbBackend As DAO.Database
Dim tdfBackend As DAO.TableDef

Set dbBackend = OpenDatabase("F:\Folder\File.mdb")
For Each tdfBackend In dbBackend.TableDefs
If (tdfBackend.Attrbutes And dbSystemObject) = 0 Then
' tdfBackend is a non-system table.
' Check whether you already know about it...
End If
Next tdfBackend

dbBackend.Close
Set dbBackend = Nothing


No offense, but in my opinion something's wrong if your users are adding
tables to the backend of a shared database!

 

[Ben]

Hi Doug,

No offense, but in my opinion something's wrong if your users are adding
tables to the backend of a shared database!

I completely agree what you are saying. Except, we are working with an
external who badly designed this structure. OUr mgmt does not care any less.

Thanks for sharing with me the code snippets.

Ben

 

[Ben]

Hi David,

HOW do the users a table to the backend using the frontend? Are you referring
to users linking to existing tables that they weren't previously linked to or
to users actauly creating a new table.

I am referring both. The reason is that the application is actually a
vendor app and the database was badly designed. I was called in to create a
front end process to mitigate, but when I was shock when I learned that new
vendors this application stores has to be in the form of a new table. That
means every vendor the firm uses, past present and future has to be in a
table of it's own. Since this is being used by end user, they have to be
able to add new vendors to the database.

But if user one adds a new table to the BE and then creates a link, the link
is not in the other FE's table definition that are used by other users. Each
user have a a copy of the FE. So I would have to do a scan each time, so
that each FE is updated with a new link to any new BE table. It's crazy.

Thanks,

Ben

 

[Marshall Barton]

The reason is that the application is actually a
vendor app and the database was badly designed. I was called in to create a
front end process to mitigate, but when I was shock when I learned that new
vendors this application stores has to be in the form of a new table. That
means every vendor the firm uses, past present and future has to be in a
table of it's own. Since this is being used by end user, they have to be
able to add new vendors to the database.

But if user one adds a new table to the BE and then creates a link, the link
is not in the other FE's table definition that are used by other users. Each
user have a a copy of the FE. So I would have to do a scan each time, so
that each FE is updated with a new link to any new BE table. It's crazy.

My sympathies ;-)

 

[David H]

I'd stop right there and argue up and down that you need the time (and money)
to modify the BE database accordingly as it obviously violates the Rules of
Normalization. If however, its a situation where you need to keep the records
isolated based on the Vendors (ex: you're an HR outsourcing firm that
provides technology solutions to multiple companies whereby all data has to
be siloed) then its a matter of one Access database per vendor.

At any rate, feel free to through the designer right under that bus.

 

[David H]

Now that I read that, that is just utterly f*****up. I'd probably go with a
startup routine that deletes all linked tables and then relinks to all the
tables in the back end. I'd then modify the process by which a new table is
added to automatically send out an email notice via Outlook advising the
users so that they know to shutdown and restart (if they choose).