AutoCAD Map 3D General

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

Rebuilding access database but retaining links

208 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: 1,837
Registered: ‎12-15-2006
Message 2 of 16 (188 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

*Expert Elite*
antoniovinci
Posts: 1,573
Registered: ‎06-03-2011
Message 3 of 16 (180 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: 412
Registered: ‎03-24-2004
Message 4 of 16 (169 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: 1,837
Registered: ‎12-15-2006
Message 5 of 16 (166 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

Contributor
Skittles11688
Posts: 13
Registered: ‎07-29-2011
Message 6 of 16 (157 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 (156 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 (154 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: 412
Registered: ‎03-24-2004
Message 9 of 16 (147 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

 

 

Active Contributor
luc.vanlinden
Posts: 44
Registered: ‎03-23-2012
Message 10 of 16 (144 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

You are not logged in.

Log into access your profile, ask and answer questions, share ideas and more. Haven't signed up yet? Register

Announcements
Are you familiar with the Autodesk Expert Elites? The Expert Elite program is made up of customers that help other customers by sharing knowledge and exemplifying an engaging style of collaboration. To learn more, please visit our Expert Elite website.

Need installation help?

Start with some of our most frequented solutions to get help installing your software.

Ask the Community