Create A UniqueID Without Autonumber

[1LongShot]

I am a draftsman running a program that adds an Access DB onto AutoCad. When I insert a valve block in Autocad, a record is created in the tblValve with the Unique ID the autocad scratch DB assigned it. That value is numeric and has no meaning. When that record is created, the software also looks into a proprietary table and moves some other values into the record. I need to use one of these values (TAG_) plus a unique numeric value to created by Access to make a concatenated field value. This HAS to happen when the record is created and not change unless a user changes it manually. This value will populate an attribute on the drawing with the valve's unique ValveID_. It cannot be a query because most of our users do not have direct access to the database, instead are using an input box inside of Autcad, and so cannot run the query.

Owen Whitehouse
Broin & Associates

 

[willir]

MichaelRed FAQ may help...
Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number) - faq700-184

 

[1LongShot]

Yeah, I looked at that. WAY too complex for what I need to do, and I specifically require no queries. I just need an automatic concatenation of two fields into one, one a unique integer and the other a value that is filled in automatically by the software. Thanks for the fast response, though.

Owen Whitehouse
Broin & Associates

 

[PHV]

As access lacks triggers you may consider using a view (aka saved query):
SELECT *, TAG_ & [Unique ID] AS ValveID_
FROM tblValve

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ZmrAbdulla]

Pls see
"Autonumber" without autonumber
at candace-tripp
regards

Zameer Abdulla
Visit Me
Where to pay your [purple]Tsunami[/purple] relief donations?

 

[1LongShot]

If our users are using AutoCad without direct access to the database, how can they use a form? I need to have the number created when the record is created, not after.
Many thanks,

Owen Whitehouse
Broin & Associates

 

[willir]

running a program that adds an Access DB onto AutoCad ... insert a valve block in Autocad, a record is created in the tblValve with the Unique ID the autocad scratch DB assigned

We will need to know more about what you mean by above quote from your original post.

It sounds like you have an AutoCAD plug-in that adds records to an Access database. You may have to talk to the developer.

 

[1LongShot]

willir,

Quite right. The app is written in ObjectArx and creates an interface between AutoCad and Access. The idea I'm working on is purely inside Access, however. When a record is created, I need to have this automatic numbering system fire up and create a valve tag. Since Access does not offer table triggers, is there a workaround that approximates a trigger's function? I have written a (ugh!) macro that will do what I want, but will not run when I want it to.....that is; upon record creation.

Owen Whitehouse
Broin & Associates

 

[PHV]

How is autocad retrieving info from access ?
Can't you get the data from a saved query (aka view) instead of a table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[1LongShot]

The code the app uses to move data btween Access and Autocad is locked down. Using a saved query means giving the draftsman access to Access, which is not what we want. Even if we did that, how does a saved query dynamically update itself upon record creation? Don't you have to run the query via some user input? I might mention here that the DB MUST be closed while the app is running. This means a user cannot initiate these saved queries. It needs to be automatic.

Thanks for your help!



Owen Whitehouse
Broin & Associates

 

[pbrodsky]

Just a thought. Does MSDE support triggers? If so, would they work if an MSDE table was linked to an Access database?

 

[willir]

Owen

Does the ObjectArx vender use ODBC to update Access, and is their plug-in flexible enough to allow for tweaking. If so, PHV's solution would be a great approach.

Have you asked the vender since this is a primary part of the issue, and I suspect many of us have not worked with ObjectArx?

Lastly, a work-around solution may be to run an UPDATE query on Access, perhaps several times a day that updates the tag information per your requirements. The update query would run update the centralized database but would use it's own Access front end.

Hopefully, we are moving forward.

Richard

 

[1LongShot]

willir

Yes, they use an OBDC plug-in. I do not, however, feel that I am competent enough to fool around with it. I have been using update queries to do what needs to be done, but I had hoped to find a more elegant solution. I need to do some more reading about MSDE. If I can use it to spawn a linked table, then perhaps I can use an SQL trigger to enable my tag creation function to run upon record creation.

Keep the great ideas coming, and thanks!

Owen Whitehouse
Broin & Associates