My company has an access database that has all of our service data on it. I am currently considering rebuilding the database because it is a database with one table and a million fields. I want to split the database up into separate tables with relationships. All of these tables would be linked based on the same key field Service_ID.
Now here is the hard part. The data would be in a one to many relationship with one record for service info and multiple records per service for pipe info. The data is currently linked to objects in AutoCAD Map 3d 2014 based on the Service_ID key field and a "service" link template.
If i rebuild this database, am I going to lose all of my links? or will i retain the links? Also, is there a way to automatically generate links based on the service_ID field? For instance there are 3 records of pipe information for 1 service. Previously all of these records would be in the same table, but now the tables are split. Since they have the same Service_ID as the Service information, will the links show up?
Hello from France
I imagine that you are speaking about ASE-SQL links which use "Link Gabarit"
- Sorry I dont know the right US/English term !?
If you are speaking about FDO, sorry please ignore this msg !
If YES, you must know that then the MDB (through the "Link Gabarit") is linked to entities,
they are in fact some special Xdatas on each concerned entity
For example, a XDLIST (from Express Tools) on the famous "DB_SAMP.dwg" :
Initialisation... Choix de l'objet: ...
Enter application name <*>:
* Registered Application Name: DCO15
* Code 1071, 32-bit signed long integer: 1
* Code 1071, 32-bit signed long integer: 1
* Code 1000, ASCII string: RoomLink1
* Code 1004, Binary data not printed. <---- You can't see an important Info !!
Object has 16341 bytes of Xdata space available.
And in Lisp :
... ("DCO15" (1071 . 1) (1071 . 1) (1000 . "RoomLink1") (1004 . "073630363400"))
Some Info are hard coded into the entity !
So you can't change anything in your table, because if ACAD will fail to retrieve the right record through the "Link Gabarit"
If an entity is linked to 3 records in the SAME table and you will split to 3 different Tables,
the entity must have 3 different "Link Gabarit" attached and of course 3 different Xdatas attached (not one)
Anything is possible, but I think it will require some high level programming in Lisp/VLisp with the old ASE-SQL module of ACAD ...
Maybe an specialist of this forum will a genious idea !?
PS: if I remember well, the original ASE-SQL module of ACAD which appears with ACAD R12 has been developped by a Russian Team for Autodesk !?
If i rebuild this database, am I going to lose all of my links?
In the whole world, you won't find anybody daring to answer to such a question, sir.
Make a couple of copies of all stuff, then try by yourself, and let us know the results, thx.
From my experience, Access will begin to slow down at apprimatively 50000-200000 records per table !
It depends from the speed of the PC and especially the speed of Hard Disk ...
When I have 1-5 million records per table, it's a big table for me (in the Geospatial World / GIS)
but a friend speaks about big table when he has more than 100-500 millions records
The life is often a referential problem ...
Sorry. The "million" was an exaggeration. What i was trying to get across is that there is one table and everytime they want to add new information they just add another field to the table and it keeps growing and growing and that is a bad way to manage data in access. The best way is to have multiple tables with relationships.
There are definitely not a million records. there are about 12,000 records on this table. The "million" was an exaggeration meant for the fields, there are way too many fields in one table and it is causing stale data.
I was afraid of this. I will let you know how things work out, but after working on this for awhile, im starting to think that it is just going to be impossible and the database will have to stay as is, which is unfortunate.
don't give up just yet
describe your data and map link. The data is linked to points, poylines, etc? and each point relates to data of the object id, description, etc?
and your data table is a mess, simply put as most flat db tables are?
a service = a point?
a pipe = a line or pline?
any other objects in the system? meters, connections, vents, pumps, etc?
It has been a couple of years since we used the Link Templates, but we did quit a lot of reengineering on the datasources etc.
It is possible to relink your entities to other tables or even other datasources, as this is what the concept of the link templates is all about (as long as the keys remain).
Via the Edit link Template properties (MAP Classic workspace > Map Data and Analysis > Database > Edit link Template properties) you can redefine to what database and what table your entities will be linked. Not sure if the fieldname as such could be remapped or not.
Also through lisp ado api we done quit a lot of these exercises. This is what I think is required based on your multiple table scenario you described, although this would be subject to a closer look at your new database structure.
Some scenarios we went through were about exporting handle and linktemplate key value. Iterating over that result and assigning/creating a new link using the original value.
Should you require more assistance on your specific situation please feel free to contact me offline at luc DOT vanlinden AT gmail DOT com.