AutoCAD Map 3D General Discussion

AutoCAD Map 3D General Discussion

Reply
Contributor
Skittles11688
Posts: 13
Registered: ‎07-29-2011
Message 1 of 16 (273 Views)

Rebuilding access database but retaining links

273 Views, 15 Replies
10-01-2013 02:07 PM

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?

 

Thanks!!

*Expert Elite*
braudpat
Posts: 2,407
Registered: ‎12-15-2006
Message 2 of 16 (253 Views)

Re: Rebuilding access database but retaining links

10-01-2013 09:57 PM in reply to: Skittles11688

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" :

 

Commande: XDLIST
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 !?

 

Bye, Pat

 

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 !?

 

 

 

 

 

Bye, Pat ( Supporting Troops )
If you are happy with my answer please mark "Accept as Solution" and if very happy please give me a Kudos (Compliment) - Thanks
*Expert Elite*
antoniovinci
Posts: 1,785
Registered: ‎06-03-2011
Message 3 of 16 (245 Views)

Re: Rebuilding access database but retaining links

10-02-2013 01:27 AM in reply to: Skittles11688

Skittles11688 wrote:

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.

Valued Mentor
parkr4st
Posts: 475
Registered: ‎03-24-2004
Message 4 of 16 (234 Views)

Re: Rebuilding access database but retaining links

10-02-2013 04:51 AM in reply to: Skittles11688

and perhaps you can explain "a database with one table and a million fields" ? 

 

dave

*Expert Elite*
braudpat
Posts: 2,407
Registered: ‎12-15-2006
Message 5 of 16 (231 Views)

Re: Rebuilding access database but retaining links

10-02-2013 05:30 AM in reply to: parkr4st

 

Hello

 

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 ...

 

Bye, Pat

 

Bye, Pat ( Supporting Troops )
If you are happy with my answer please mark "Accept as Solution" and if very happy please give me a Kudos (Compliment) - Thanks
Contributor
Skittles11688
Posts: 13
Registered: ‎07-29-2011
Message 6 of 16 (222 Views)

Re: Rebuilding access database but retaining links

10-02-2013 06:43 AM in reply to: parkr4st

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.

Contributor
Skittles11688
Posts: 13
Registered: ‎07-29-2011
Message 7 of 16 (221 Views)

Re: Rebuilding access database but retaining links

10-02-2013 06:45 AM in reply to: braudpat

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.

Contributor
Skittles11688
Posts: 13
Registered: ‎07-29-2011
Message 8 of 16 (219 Views)

Re: Rebuilding access database but retaining links

10-02-2013 06:46 AM in reply to: antoniovinci

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.

Valued Mentor
parkr4st
Posts: 475
Registered: ‎03-24-2004
Message 9 of 16 (212 Views)

Re: Rebuilding access database but retaining links

10-02-2013 07:28 AM in reply to: Skittles11688

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?

 

dave

 

 

Valued Contributor
luc.vanlinden
Posts: 50
Registered: ‎03-23-2012
Message 10 of 16 (209 Views)

Re: Rebuilding access database but retaining links

10-02-2013 07:49 AM in reply to: Skittles11688

Hi

 

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.

 

Regards

 

Luc

Post to the Community

Have questions about Autodesk products? Ask the community.

New Post
Announcements
Do you have 60 seconds to spare? The Autodesk Community Team is revamping our site ranking system and we want your feedback! Please click here to launch the 5 question survey. As always your input is greatly appreciated.