variable link from AutoCAD to MS Access

Suggestion for your consideration: Translate this question to Access. Make litle or no reference to AutoCad.

In Access you have...

You want to do what with it?

Access is manipilating AutoCad or the other way around?

Mike

Neither is manipulating. AutoCAD is reading data located in an access database.
Or can a variable in AutoCAD link and show info in a database field from Access?

Again to simplify, I have a table in access with two fields called ctrlJobNmbr and ctrlDwgNmbr. When I start a new Cad file, I want two variables in the dwg file to display the matching data in these two fields based on the ctrlDwgNmbr and the AutoCAD dwg number being equal. Maybe using RText or something else will work. Since this encompasses 2 programs I will up the anty to 500 points if you have to share expertise. Or just award all to you. Additionaly, there is more info I want to link, but if you can do it with one 4 or more should not be a problem, maybe?

It is not question of points here. I started Autocal from version 2.5 and stoped at version 14.

I am interested in autocad and would like get into some automation tasks and this is why I am in this thread. I am a bit rusty though and it will take a bit for me to come to speed.

Also, make sure this question has a link to AutoCad area as well.  Now, later this evening I will be reading this question again to see if I could be of some help.

Mike

I don't use "access".  But, if you go to the 'Help' menu in Autocad and under Index tap type in 'Access' and look for "setting up with ODBC".  This may get you started.  Autocad is becoming more MS friendly all the time.

Just as pseudo code:
Ask for the ctrlJobNmbr and ctrlDwgNmbr with a form. in the background (I prefer jet) select the information that is required for the title block, itterate through the paper space layouts and update (after checking to make sure the title blocks have the incorrect data) all of the title block attributes with the results. On a new drawing check to see if the variables are filled if not fill the vars if so check the title blocks.

If you choose to do as dhsindy suggest, remember to set it up with a DSNless connection otherwise you will need to do this on each PC.

HTH
dragontooth

Please close question. No answer was acceptable and the answers given where to vague or where not close enough to use. The comments where not from the area in question and I believe the points where primary interest and not in finding the solution. Thanks in advance

Hi cazink,

It has taken you over 3 months to post back, the suggestions that have been previously posted are an attempt to get a better understanding of what You are trying to do, are you asking what the SQL string should look like? how to connect to Access? how to open a drawing in Acad fill in a title block with data from access copy the information to different layouts based on what?

Based on the information provide I would suggest that the question be deleted and points not refunded.

Thank you for your attention in this matter,
dragontooth

cazink,

What your try to accomplish is vary specific and I'm sure you would like it streamlined.
I would suggest writing a custom VB app. that reads and/or writes to an access database.

A few resources to get you going.
"Using Visual Basic with AutoCad" By Andrew G. Roe
and or
"AutoCad Visual Basics   A guide for the Non-Programmer".

Both of these have examples of what you are trying to accomplish.

These proposed answers are not typical of the help I usually receive, but seem to point in a direction of wanting the points without exerting the effort. It also appears that no one read my request more than once. I am aware that trying to explain what I am trying to do can be misunderstood but really, I mention the data is in Access and then into Autocad with the information not the reverse as you have mentioned. I never mentioned SQL. One of the responses actually said they do not use Access and look to the help menu. The experts in my opinion had better questions to answer than mine in this occasion. Enough from me now, I apologize for my rudeness and the lake of perceived help form those who tried.

You have a block that you want the information populated when the user makes a new drawing.
"users1" is a drawing variable that can be used for storing information like, if the information is filled, set it to "TBInfoFilled" and do not process, for example. users1, users2, users3, users4, users5 all store text information with the drawing.
The macro would have to fire on the AcadDocument_Activate as far as I can tell. If you are using VBA in Acad
Or you could use an OCX from Acad or OpenDwg to read and write to a file. If you are using a programming language other than VBA.
You could use "dbconnect" in the drop down menu Tools (as suggested) to populate the information in the title block (which deals with SQL for Access). Or update ....
There are so many ways to do what you ask/request.
Is the dwg new (from a template)?
Is the dwg a copy of another drawing that gets modified?
Are you creating this through a script from the estimating software you wrote?

I don't know where you are or how you want to do this.

dragontooth

OK lets try one more time. First If I use user1 etc. there are not enough of these to actually fill out a title block efficiently, unless I am mistaken there were only 4 or 5 user fields. Then the drawing is created as new dwg from a template. When the dwg opens the ideal something would be that the dwg's name such as 6578.dwg would be the key to linking together the dwg and the access database. Where the attributes in the dwg would be populated with the corresponding values in access where the dwg number is the common link.
Access       DWG
table ID      6578
name          name
address      address
Etc              etc
Now to quote you"There are so many ways to do what you ask/request."
that I am sure you can give me only one that works and is easy to implement. I am aware of the possible areas you have mentioned but tried to rely on The Experts saving me time by not reinventing the wheel. All the responses have been vague and non specific. I could have answered my own question the same way.

cazink,

There is no canned way of doing what you would like. You'll need to write some code to accomplish this.

This is what I would suggest.

1.Set up an access database with the table and fields you require. (DWG name, Name, Address Etc)
2. Enter some test data into the table.
3. Write a VBA program that you can run and connect to access.

Program would work something like this.
A. Setup connection to database.
B. Get value of dwgname variable.
C. Look up dwg name in access.
D. Read in corresponding fields to some variables. (DWG name, Name, Address Etc)
E. Find block and update attribute tag information with corresponding access fields.
F. Close database connection.

As I said before, I would pickup one of the books I mentioned in my previous post.
They show good examples of programs that do very similar functions to what you are trying to do.

Here is a link that may get you pointed in the right direction.
http://discussion.autodesk.com/thread.jspa?messageID=3964882

The problem is that everybody knows how to do brain surgery. You start with some one's head and then you drill holes. The problem is keeping the person breathing. I do not write in VBA except in Access. I understand the overall idea of what should happen, I am asking for a solution to the problem not a discussion on what the overall idea. Thanks for your help and ideas but I believe the message is now closed.

I don't think everybody knows how to do brain surgery. If you know that much and how to write in VBA  in Access then you should be able to write a little VBA in AutoCad to accomplish what your looking for. You have been given multiple responses that in my opinion have answered your questions short of writing code for you. If your not willing to do a little work then maybe you should pay a programmer to help you out.

I may agree. I was hoping that somewhere someone would have done the exact thing I am trying to accomplish so I would not have to write code, even though I knew this would probably be the end result. 250 points means to me that more than a suggestion on how the process is to operate or a couple of links to follow. I hate taking this cynical approach to this but look at the time wasted with these conversations. And if it was that easy you would have written it already and I might have upped the 250 to 500 points and withheld the cynicism. OK last comment, your link above, I have seen already. Thank you.

you probably wont like my suggestion but I'll throw it in anyway just in case it helps you.
I have done something similar to what you are trying but with object data instead of attributes. you will probably have to write code to do what you are asking. since we dont want to reinvent the wheel start out with checking this link:
http://usa.autodesk.com/adsk/servlet/item?siteID=123112&id=3027392&linkID=9240615

the rest of the series can be found here:
http://usa.autodesk.com/adsk/servlet/autoindex?siteID=123112&id=2253435&linkID=9240615

here is a snip of code that I use to get the connection for my objeject data, not sure where I got it from but I know that I didnt create it:
Private Sub cmdInitDbConnect_Click()
    ThisDrawing.Activate
    ' Since CAO16.dll must be run inside AutoCAD process space,
    ' We MUST use AcadApplication.GetInterfaceObject(ProgID) to create any CAO objects.
    ' We cannot use normal CreateObject() or New to create any CAo objects
    Set dbConnect = GetInterfaceObject("CAO.DbConnect.16")
    If dbConnect Is Nothing Then
        MsgBox "Unable to create CAO.dbConnect Automation server."
        Exit Sub
    Else
        Dim msgStr As String
        msgStr = "CAO.dbConnect Automation server object is created!"
        msgStr = msgStr + vbCrLf + " Version = " + dbConnect.Version
        'MsgBox msgStr
        'cmdInitDbConnect.Enabled = False
        'cmdGetLinkTemps.Enabled = True
'        cmdGetLabelTemplates.Enabled = True
'        cmdReloadLabels.Enabled = True
        'cmdErrors.Enabled = True
    End If
End Sub

*******
this some code that I altered from those links I posted to fill out a user form in autocad:
Public Function populate_dropdown(WHAT As Integer)
 SRC_JOB_NO.Clear
 SRC_BY_LOC.Clear
On Error Resume Next
Dim sqlst3, locSql As String
Dim COUNTFILES As Integer

Select Case WHAT

Case 1
sqlst3 = "SELECT *" & _
"FROM db WHERE (((whatever.AS_BUILT)=True))" & _
"ORDER BYdb.JOB_NO;"

locSql = "SELECT *" & _
"FROM db WHERE (((whatever.AS_BUILT)=True))" & _
"ORDER BY db.LOCATION;"

COUNTFILES = 0

Case 2
"more cases that determine my sql string"
End Select

  Set dbsObj = DBEngine.Workspaces(0).OpenDatabase("path/:db.mdb")
    Set rstobj = dbsObj.OpenRecordset(sqlst3, dbOpenDynaset)
  Set rstobj2 = dbsObj.OpenRecordset(locSql, dbOpenDynaset)
  Do While Not rstobj.EOF
      SRC_JOB_NO.AddItem rstobj!JOB_NO
           COUNTFILES = COUNTFILES + 1
      HOWMANYFILES.Caption = COUNTFILES & " FILES"
      rstobj.MoveNext
  Loop
 
  Do While Not rstobj2.EOF
      SRC_BY_LOC.AddItem rstobj2!LOCATION
      COUNTFILES = COUNTFILES + 1
      HOWMANYFILES.Caption = COUNTFILES & " FILES"
      rstobj2.MoveNext
  Loop

End Function
that code will make more sense after you read those links. but if you can get a userform populated you should be able to get an attribute populated.
here is some code to do that, adds path and datestamp to an attribute:
Sub CurrentDate()
Dim AttArray As Variant
Dim obj As Object
Dim msg, Style, Title, Help, Ctxt, response, MyString


JUSTPRINT:
Dim blkref As AcadBlockReference


If ThisDrawing.GetVariable("Ctab") = "Model" Then

    For Each obj In ThisDrawing.paperspace
            If TypeOf obj Is AcadBlockReference Then
            Set blkref = obj
                If blkref.HasAttributes Then
                    AttArray = blkref.GetAttributes
                    For i = LBound(AttArray) To UBound(AttArray)
                    If AttArray(i).TagString = "DATESTAMP" Then
                        AttArray(i).textString = "whatever you want'_
                       
                    End If
                    Next i
                   
                End If
        End If
    Next
   
    For Each obj In ThisDrawing.paperspace
            If TypeOf obj Is AcadBlockReference Then
            Set blkref = obj
                If blkref.HasAttributes Then
                    AttArray = blkref.GetAttributes
                    For i = LBound(AttArray) To UBound(AttArray)
                    If AttArray(i).TagString = "DATESTAMP_ONLY" Then
                        AttArray(i).textString = Now
                    End If
                    Next i
                   
                End If
        End If
    Next

End If

TRYAGAIN:
End Sub

I would suggest if you dont want to go through the trouble to learn how to do this with vba then you may look into using the sheet set manager in conjuntion with fields to make filling out your title blocks easier, all that is in the help files depending on which version of autcad you are using I guess, but you didnt mention which one.
and your last comment is correct ,it is not easy, its actually a royal time consuming pain, and to me writting code for something that extensive is not worth 500 points anywhere, unless I have nothing else to do, but trying to help someone figure it out is worth something even if there is no points involved,
anyway good luck and hopefully you can make something that will work.
and no offense meant.

Just a little response to your help: I am the senior estimator for a poured concrete wall company, and I am responsible for the estimating program I wrote in Access, the Lisp routines I wrote in Autocad for our field drawings, and the other Office programs which keep the paper flowing. So therefore I am very busy, but knowledgeable in many areas. But not an expert in any one. Therefore I get a little upset when someone leaves a general vague idea response to my question. I at this time do not have the time to try what you have left, BUT I am awarding the points to you BECAUSE you realize that this is a royal pain and you actually left some code that shows you are willing to do a little brain work for the points you are receiving. Thank You, Charly

thanks for the points.
hopefully next year I will be able to get back on track with the access/autocad connectivity project that I started. if I do I might start a blog about it, since that seems the thing to do nowadays
good luck with your project, if I come up with some better code I'll post it.