Two Databases Sites

[Greg]

I've have designed a database which I have Connected to Autocad, which I use
for Document control.

However I have two other sites using the DB, however from time to time I
need to copy sections of tables between my local DB & the External DB's,
which is causing problems.

The problem is that the Autocad Documents store a Document_ID which is an
Autonumber in Access. So when Drawings arte transfered between sites, and the
related records (I am using XML Import/Eport) the Document_ID changes in
Access.

I think there are probably a couple of ways around this problem, one being
an Auto_id function, so that each record has a unique Id with a prefix for
each site. Is it possible to create a function, that can be used in a table
design ?

Also I don't know if setting up sharepoint service on my server would help.

I suppose I'm looking for some guidance, readings etc that will point me in
the correct direction to resolve this Problem. So any help would be greatly
appreciated.

 

[Stefan Hoffmann]

hi Greg,

The problem is that the Autocad Documents store a Document_ID which is an
Autonumber in Access. So when Drawings arte transfered between sites, and the
related records (I am using XML Import/Eport) the Document_ID changes in
Access.

Autonumbers are only for ensuring the uniqueness of that value. As they
often are a surrogate key, they should never be used outside the system.

I think there are probably a couple of ways around this problem, one being
an Auto_id function, so that each record has a unique Id with a prefix for
each site. Is it possible to create a function, that can be used in a table
design ?

Yes, can easily create the next number:

Nz(DMax("ID", "yourTable"), 0) + 1

Also I don't know if setting up sharepoint service on my server would help.

Using Access 2007, yes, you may use a SharePoint list instead of a
table. Otherwise, if the other sites can connect to your sharepoint
service, than you may consider using one shared backend .mdb.


mfG
--> stefan <--

 

[Fred]

I noticed that nobody answered.

Your underlying problem is that what you describe as A (one) db, and what
would be best run as A (one) DB you (by allowing entries on all three, and
copying blocks of records from one to the other) are, letting many things
about it run as 3 DB's. This will cause many problems and complexities, the
one that is the subject of your post being just one of them.

If there is ANY way that you could run this as ONE db, your would be much
better off. Two of the possibilities could include:

Direct remote access to the one copy (back end) from all three locations.

Do all data entry at only one location, and have them send COPIES of the
entire db to the other 2 locations for look-up purposes (only)

Otherwise you'll need a more complex replication process.

Fred

 

[Greg]

Ok, so if I have a seperate Unique Number from the Autonumber field, how do I
make each new entry in a "Doctable" = Nz(DMax("ID", "Doctable"), 0) + 1. I am
assuming an insert query is the only way. I'd really like to have a function
like the builtin autonumber.

 

[Stefan Hoffmann]

hi Greg,

Ok, so if I have a seperate Unique Number from the Autonumber field, how do I
make each new entry in a "Doctable" = Nz(DMax("ID", "Doctable"), 0) + 1. I am
assuming an insert query is the only way. Yes.

I'd really like to have a function like the builtin autonumber.

You can use the expression directly in a SQL statement or in code. Or
you encapsulate it in a function placed in a standard module:

Public Function NewDoctableID() As Long

NewDoctableID = Nz(DMax("ID", "Doctable"), 0) + 1

End Function




mfG
--> stefan <--

 

[Greg]

So can I use this function in a table directly? HOW?

 

[John W. Vinson]

So can I use this function in a table directly?

No. Stefan gave you a VBA function that you can call... but tables have no
events. If you use a Form to enter data you can apply his code in the form's
BeforeInsert event.

Table datasheets are not designed for interacting with data or for users to
see at all - they're for data storage. You should only enter data via a Form.