AutoCAD Map 3D Forum
Welcome to Autodesk’s AutoCAD Map 3D Forums. Share your knowledge, ask questions, and explore popular AutoCAD Map 3D topics.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Rebuilding access database but retaining links

15 REPLIES 15
Reply
Message 1 of 16
Skittles11688
511 Views, 15 Replies

Rebuilding access database but retaining links

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

15 REPLIES 15
Message 2 of 16
braudpat
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 !?

 

 

 

 

 

Patrice ( Supporting Troops ) - Autodesk Expert Elite
If you are happy with my answer please mark "Accept as Solution" and if very happy please give me a Kudos (Felicitations) - Thanks

Patrice BRAUD

EESignature


Message 3 of 16


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.

Message 4 of 16
parkr4st
in reply to: Skittles11688

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

 

dave

Message 5 of 16
braudpat
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

 

Patrice ( Supporting Troops ) - Autodesk Expert Elite
If you are happy with my answer please mark "Accept as Solution" and if very happy please give me a Kudos (Felicitations) - Thanks

Patrice BRAUD

EESignature


Message 6 of 16
Skittles11688
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.

Message 7 of 16
Skittles11688
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.

Message 8 of 16

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.

Message 9 of 16
parkr4st
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

 

 

Message 10 of 16

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

Message 11 of 16
Skittles11688
in reply to: parkr4st

Here is an example of Data as it is now.

 

DataExampleNow.jpg

 

Here is how I want it to look

 

Service Info Table

     DataExampleGoal.JPG

 

It is a Service Info Table with all the generic service information and then a pipe data table with the specific pipe information related to that service. They are linked through the Service_ID which is the Key field in the Service info table. One service may have multiple sections of pipe so those sections would have the same service ID as the Service info table, but will have their own Unique_ID which is the Key field in the Pipe data table.

 

as of right now all the information is on the one table and each record is linked to a poly line in autocad representing the service pipe.

 

There are meters but those are not visually represented. Valves are visually represented but are their own DWG file and their own access database.

 

Thank you for your help!

Message 12 of 16

This was great! it did one step of what i needed to do. Now they have been relinked to the service info table, but is there a way to get the pipe data table to link to the poly lines as well? The pipe data table has the same service Id as the service info table. I wish there was an easy way to duplicate the link template and then change it to the pipe data table as well. this would solve my problem i think.

Message 13 of 16
parkr4st
in reply to: Skittles11688

Water or gas services?

 

I would have guessed the service would be a point at the building served (ie. meter) and the pipes as polylines are pipes linking a main to the services.

 

service db would have id, xyz, customer/user id, meter#

 

pipe data related to the service through the service id.

pipe record #, pipe id, service id, pipe type, length

 

customer id related to the customer data table

 

pipe type related to a pipe info table where the material, date if purchase, manufacturer, and such relevant data is kept.

 

meter table would hold the meter manuf, new date, etc.

and the valves would fit into the piping scheme somehow

 

This may be a candidate for Industry model for water or gas? 

 

 

dave

 

 

 

 

Message 14 of 16
Skittles11688
in reply to: parkr4st

They are gas services. This is a database and DWG i inherited when i took over a retirees position. It was all set up by him. I completely agree with you that there are a billion better ways to do things, I am sure of this.

 

Just for background this is a local government entity, and resources are scarce, including training and IT or people with general knowledge of computer programs and how they work.

 

If i knew how to break up everything and set it up in a standard way that is common to many other gas companys, i would absolutely do so, but i am not sure how to go about it. It also all needs to work with our annual reporting which i do through access queries. I absolutely do not mind making new queries, as this will be necessary, but i need to be sure that i can still query out all the information that i need together. I suppose i need to do some more research. I am leaving for the day today, but will be back tomorrow.

 

Thank you so much for your help and suggestions so far! I am hoping i can get this done correctly!

Message 15 of 16
braudpat
in reply to: Skittles11688

 

Hello

 

+2 with Luc

 

A lot of special things can be done with the LInk Template of the ACAD/MAP ASE-SQL Module !

 

For example a one time you are linked to a Table named XXX with a Key Fileld YYY through a Link Template named ZZZ

You quit ACAD ...

You work into Access (or Oracle) and now with the same names of Table and Key Fiield :

it's not a Table but in Fact NOW it's an other Table or Query (Or View in Oracle)

 

As long as you maintain the names of the Table/Query/VIew and the Key Field Type & Name,

your entities are always linked to Access/Oracle/etc ... If the corresponding Record exists of course !

 

May others tricks are possible ...

 

Bye, Pat

 

 

Patrice ( Supporting Troops ) - Autodesk Expert Elite
If you are happy with my answer please mark "Accept as Solution" and if very happy please give me a Kudos (Felicitations) - Thanks

Patrice BRAUD

EESignature


Message 16 of 16

I thank all of you for all your help. You all got me part of the way there. Unfortunately it seems that the rest of what I want to do is going to take some lisp coding which I am not familiar with. I am going to have to wait until my company can get the resources to do this the right way. Until then I will just have to manage what I have. Thanks again! Everyone has been so helpful!

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report

”Boost