VBA - Mini-Doc Mgmt - MSACC97 ACAD2K4

[chill3490]

To those interested,

I am just learning VBA ( ATP courses, et al) and would like to employ it in setting up a mini-document mgmt system using both MS Access 97 (DAO) and AutoCAD 2004 and would like to bounce ideas off people doing roughly the same thing...i.e. using VBA to extract dwg info from block attributes , semi-automatic xfer and/or updating of data between app's, etc.


Regards,

Clint Hill
Designer
Win XP Pro
AcadMech2k4

[Ed Jobe]

I still have 97 at home although I use 2000 at work. We've used Cyco Automanager for years and I've written a lot code for it. Instead of upgrading, I thought about doing it myself, but our needs take it beyound Acess's capabilities. But I've done a lot of db work with Access and might be able to offer help. What ideas did you want to bounce?

[chill3490]

Ed,

With the premise of biting off little pieces of info, this thread might be spread over an extended period and I foresee it mainly dealing with AutoCAD data extraction with a foray into the db side...

With that in mind:

Would you suggest this be coded within AutoCAD VBA or MSAccess? Why?

[Ed Jobe]

It depends upon the task at hand, but probably most of your code would be in Access, it already uses DAO, so you wouldn't have to reference other libraryies from acad and that's where the object you would be working with reside. If you have to, you can call a function written in Access from withing acad, and vice versa. Do all users have Access installed? or are you using Office Developer to create a distribuatble exe?

[chill3490]

Ed,


Do all users have Access installed?The users have Access ( the company is still using MS Office 97 Pro).

Well, I guess I had better bone up on Access VBA. Luckily, I have several reference books on the subject and am enrolled in a web-based and company-sponsored Access VBA course. It is just a matter of time investment and your suggestions to keep me moving in the right directory.:)

[mtuersley]

If you want to do it right, don't even start it :) That's from 10 years of writing custom systems and customizing/installing CYCO products. But I assume you're not to be dissuaded that easily, so...

1. Forget about doing it in VBA. Download the MS Express version of VB.NET [or VB6 if you have it] and start with it because you will want to create a middleware piece; Use a DLL that can function in all apps.

2. Forget about Access - its not robust enough and corrupts easily. Download the dev version of MSDE or look into MySQL.

[chill3490]

Mr. Tuersley,

I welcome your viewpoint.

My biggest fear WAS putting into production a custom database routine that would break easily and NOT having the knowledge to analyze much less fix conflicts/corruptions as you apparently do sir.

Just to verify; you would not recommend MSAccess after considering the following points:

Our environment is made up of 4 AutoCAD users.
Our output is small (50 - 100) new cad files per/yr.
VBA, AutoCAD, and MS Office 97 Pro are the tools I currently possess.
Are these three points even relevant?
If no, I suppose your suggestions still stand?
Do you have knowledge and/or would you recommend a C-based language approach instead?

Thank you,
Clint

[Ed Jobe]

At the risk of putting words into Mike's mouth, I think he meant doing it right, in terms of a professional, robust, feature full, system. Maybe I didn't say this clearly before, but, while there are better ways, you can make Access work. I assumed you've done an analysis of buying an of-the-shelf system? It is an investment, but so is your development time, which so far, includes this time it takes you to research technologies. I know the temptation to want to do it yourself, but sometimes its better to let someone else do it. Even though you are a small team, what should drive your decision in a doc mgmt system is your document needs. Can you afford to misplace or loose a document. If not you need a secure client/server app. Do you need revision control, workflow? If you've answered all those questions, you can still do what you want in vba and be successful. It may not be what some other method might offer, but if you have a small budget and need only a few features, then vba and Access is ok. To answer Mike's questions 1 and 2. You should first do a study to determine your needs. Outline the processes you want to automate and the data you will store. HTH

[chill3490]

Ed,

Yes, small-scale is what we require for the foreseeable future. IOW, it would beat what we have now; nothing!

We would require very few features and with backups regularly performed, little risk of data loss or security issues as even the drawing title block data is of very little value to other entities. I was additionally investigating use of the MDE extension file type to avoid any obvious tampering from users.

Simple data storage of drawing title block information, new drawing number assignment( from Access) to include opening AutoCAD filling in title block attributes, paper space viewport setup, layout tab naming, performing file searches, and yes, revisioning would be included. But, this would mean only having set up fields for the drawing attributes related to revision level, description, date, name of reviser and would not lock files or any bells and whistles such as 'approval before processing' type of functionality that I understand can be had with turnkey doc mgmt systems.

All I foresee to be a potential point of major learning is exactly how to update the information in the partner app. when changes are made.

And yes, IT IS time to quit running around in .......:Puffy:

Thanks ED.

Clint

[Ed Jobe]

You should be able to do all that, provided you want to. :-) Creating new files would be handled by selecting a doc type, then loading the appropriate template (it has layouts, vps, tb, etc) and updating the tileblock. A complementary Save command in acad updates the db if the tb changes.

There are 3 ways to handle revisions. 1) Keep only the latest rev, noting its revision level, etc. 2.) Keep previous revs. Each has a unique filename. 3) Keep previous revs, each has the same filename, with previous revs in subfolders. The trouble with all of these when implemented on a lan fileserver, is that anyone can bypass the doc mgmt system and/or move folders, delete files, etc.

...But, have you checked with doc mgmt vendors on pricing and compared that with the estimated cost for you to develop and app? I recommend Adept (http://www.synergis-adept.com/). With only 4 user's, it shouldn't be too bad.

[clint.sowers]

All this seems a little out of the way to me; if you are looking to exchange info b/w ACAD & MS EXCEL you are on the right track! You should check this out:
http://usa.autodesk.com/adsk/servlet/item?siteID=123112&id=2671851&linkID=2475176
and if you really want MS Access:
http://usa.autodesk.com/adsk/servlet/item?siteID=123112&id=2971496&linkID=2475176
There is probably enough info to get you started or at least give you some new ideas! There are between 3-5 step by step walk throughs for each; excel & Access, check them all out and see if this is what you need.

[mtuersley]

FIrst off, Ed - you can put words in my mouth any time :) As to the rest, look into CYCO as they now have a free version of their AutoManager product. It is fully scalable so you can upgrade to Teamwork or Meridian any time. It would fit your need as you have described it so far.

I would *definitely* not use Access and especially not MDE! MDE is a pain in the a@# - just do some searching on it. Access dbs correct regardless of how many docs you add per year. Also, the plan I stated has nothing to do with C languages - you can create the DLL in VB. While you currently on possess VBA, VB.NET and C# are free so take advantage. If it were me, I'd do it in C# but there is the learning curve to consider.

If you were to proceed with VBA and Access, then do not start having one app open the other. Write two apps, one for each program and use a middle file such as an INI or XML that both share - it'll make life simpler. From AutoCAD, use Access directly through an ODBC connection. For Access to AutoCAD, use DBX, but I really don't see a reason why you'd need an Access version. Once the db is set up, you have no reasons/conditions stated that would require an Access interface.

Oh, I appreciate the formality but it's just Mike - sir and Mr. Tuersley are my dad =) If you need help along the way, you can always email me.

Mike

[mtuersley]

There is a difference between those links you posted and doc mgmt. The links show how to use/exchange internal data but don't deal with files. Toss the Excel one, too - you should only use it for computational issues NEVER data storage because its not designed for it.

[richard.binning]

Just thought I'd jump in here too...I agree with Mike, once you've built the database there really is no need to ever open access unless the database gets corrupt. You should be able to read and write from/to the database file without using Access. I prefer to use ado over dao for most connections myself, especially since dao is no longer being updated. Look into using ObjectDBX as Mike suggested, also look into some of the windows scripting host methods and objects since you are dealing with files, folders, etc.

There is another free doc mgr available from arup. It is called columbus, you'll find it with a google search using the key words "ove arup columbus" or by clicking this link. (http://columbus.arup.com/)

You might try heading over to the ATP forum and downloading Mike DeGraw's Access, AutoCAD, and VBA class handouts.