If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Two Databases Sites
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. -- Greg McLandsborough |
#2
|
|||
|
|||
Two Databases Sites
hi Greg,
Greg wrote: 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 -- |
#3
|
|||
|
|||
Two Databases Sites
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 |
#4
|
|||
|
|||
Two Databases Sites
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. -- Greg McLandsborough "Stefan Hoffmann" wrote: hi Greg, Greg wrote: 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 -- |
#5
|
|||
|
|||
Two Databases Sites
hi Greg,
Greg wrote: 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 -- |
#6
|
|||
|
|||
Two Databases Sites
So can I use this function in a table directly? HOW?
-- Greg McLandsborough "Stefan Hoffmann" wrote: hi Greg, Greg wrote: 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 -- |
#7
|
|||
|
|||
Two Databases Sites
On Sun, 21 Dec 2008 18:43:01 -0800, Greg
wrote: 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|