Connecting Autocad 2005 to Access 2003

[mcaraker]

I've absorbed the very helpful FAQ on here into a program I'm trying to write which would spit out the 3 attributes of a specific block (selected by the user) into a database table, one line per block. The code was to the point where I was getting one line per block, but the rows were blank. Now suddenly this morning, without making any changes I can recall, I'm getting an error on the

rstAttribs.Open , , , , adCmdText

line when I run the code (Command text was not set for the command object)...? Here's all the code;

Public AcadDoc As AcadDocument 'Current AutoCAD drawing document.
Public cnnDataBse As ADODB.Connection 'ADO connection to database.
Public rstAttribs As ADODB.Recordset 'ADO recordset to update.Global SS As AcadSelectionSet
Global BLKS As AcadBlocks
Global BLK As AcadBlockReference
Global BLK2 As AcadBlock
Global Grps(0 To 1) As Integer
Global Dats(0 To 1) As Variant
Global Filter1, Filter2 As Variant
Public Function Connect(strDatabase As String, strTableName As String)
' 1) strDatabase - The database file location.
' 2) strTableName - Table within the database to open.
' Connect "Drawings.mdb", strTblName

Set cnnDataBse = New ADODB.Connection
With cnnDataBse
.CursorLocation = adUseServer
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source").Value = strDatabase
.Open
End With

Set rstAttribs = New ADODB.Recordset
With rstAttribs
.LockType = adLockPessimistic
.ActiveConnection = cnnDataBse
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.Source = strSQL
End With

rstAttribs.Open , , , , adCmdText

If rstAttribs.RecordCount <> 0 Then
rstAttribs.MoveFirst
End If

End Function

Sub BlockCounter()
Grps(0) = 0: Dats(0) = "INSERT"
Grps(1) = 2: Dats(1) = ""
On Error Resume Next
Set SS = ThisDrawing.SelectionSets.Add("SS")
If Err.Number <> 1 Then
Set SS = ThisDrawing.SelectionSets.Item("SS")
End If
SS.Clear
Set BLKS = ThisDrawing.Blocks
J = BLKS.Count - 1
For I = 2 To J
Set BLK2 = BLKS.Item(I)
Dats(1) = BLK2.Name
Filter1 = Grps
Filter2 = Dats
SS.Select acSelectionSetAll, , , Filter1, Filter2
OUT$ = BLK2.Name
BlockCount.ListBox2.AddItem OUT$
SS.Clear
Next I
While 1 = 1
BlockCount.Show
Wend
End Sub

Private Sub CommandButton1_Click()
End
End Sub

Private Sub CommandButton2_Click()
BlockCount.ListBox1.Clear
Dim SS As AcadSelectionSet
Dim BLKS As AcadBlocks
Dim BLK As AcadBlockReference
Dim BLK2 As AcadBlock
Dim Grps(0 To 1) As Integer
Dim Dats(0 To 1) As Variant
Dim Filter1, Filter2 As Variant
Grps(0) = 0: Dats(0) = "INSERT"
Grps(1) = 2: Dats(1) = BlockCount.ListBox2.Value
On Error Resume Next
Set SS = ThisDrawing.SelectionSets.Add("SS")
If Err.Number <> 1 Then
Set SS = ThisDrawing.SelectionSets.Item("SS")
End If
SS.Clear
Filter1 = Grps
Filter2 = Dats
SS.Select acSelectionSetAll, , , Filter1, Filter2
Dim J As Integer
J = SS.Count - 1
Dim varAttributes As Variant
Dim N As Integer
For I = 1 To J
Set BLK = SS.Item(I)
varAttributes = BLK.GetAttributes
For N = LBound(varAttributes) To UBound(varAttributes)
strAttributes = strAttributes & " Tag: " & varAttributes(N).TagString & _
" Value: " & varAttributes(N).TextString & " "
Next
OUT$ = SS.Item(I).Name & " " & strAttributes
strAttributes = ""
BlockCount.ListBox1.AddItem OUT$
Next I
SS.Clear
End Sub

Private Sub CommandButton3_Click()
Connect "c:\temp\hls.mdb", "import-temp"
BlockCount.ListBox1.Clear
Dim SS As AcadSelectionSet
Dim BLKS As AcadBlocks
Dim BLK As AcadBlockReference
Dim BLK2 As AcadBlock
Dim fldAttribs As ADODB.Field
Dim Grps(0 To 1) As Integer
Dim Dats(0 To 1) As Variant
Dim Filter1, Filter2 As Variant
Grps(0) = 0: Dats(0) = "INSERT"
Grps(1) = 2: Dats(1) = BlockCount.ListBox2.Value
On Error Resume Next
Set SS = ThisDrawing.SelectionSets.Add("SS")
If Err.Number <> 1 Then
Set SS = ThisDrawing.SelectionSets.Item("SS")
End If
SS.Clear
Filter1 = Grps
Filter2 = Dats
SS.Select acSelectionSetAll, , , Filter1, Filter2
Dim J As Integer
J = SS.Count - 1
Dim varAttributes As Variant
Dim N As Integer
For I = 1 To J
rstAttribs.AddNew
Set BLK = SS.Item(I)
varAttributes = BLK.GetAttributes
fldAttribs.Value = varAttributes(0).TextString
OUT$ = "RMNO=" & varAttributes(0).TextString & " ROOMNAME=" & varAttributes(1).TextString & " " & varAttributes(2).TextString
rstAttribs.Update
BlockCount.ListBox1.AddItem OUT$
Next I
SS.Clear
cnnDataBse.Close
End Sub

Basically there's a form with 3 buttons and 2 list boxes. When I run the macro BlockCount I get a list of all the blocks in the current drawing in listbox1. I can then select one of them and then click command button2 to get a list of each instance of the selected block along with all its attributes in listbox2. Then clicking command button3 is what spits the information into MS Access into a database file called hls.mdb in the table called import-temp. The next step will be to run some access macros to turn that table into a report saving the user the step of typing in all the room name and numbers when they are doing a survey of the building for code violations.

Any help would be greatly appreciated! I'm quite new at this and resourses like this seem to be the only things around that help!

 

[TCARPENTER]

Hi Mike,

Your function: Public Function Connect(strDatabase As String, strTableName As String) has two problems when it comes to opening a recordset.is looking for a table name. First, is you are using strSQL (it has no value), for your recordset source, the second, is the adCmdText option, this is used for SQL strings, not tables. So your code should look like this:

  With rstAttribs
    .LockType = adLockPessimistic
    .ActiveConnection = cnnDataBse
    .CursorType = adOpenKeyset
    .CursorLocation = adUseServer
    .Source = [b][purple]strTableName[/purple][/b]
  End With
  
  rstAttribs.Open , , , , [b][purple]adCmdTable[/purple][/b]

HTH
Todd

 

[mcaraker]

Thanks Todd.

I just made those two changes in my code and when I run it, I think it gets farther because it seems to chew on things for a few seconds before I get the following message;

syntax error in FROM clause

When I hit debug it points me back to this line again

rstAttribs.Open , , , , adCmdTable

Rather than driving you nuts with this thing, is there a reference manual you'd recommend? I greatly appreciate your help thus far but I feel bad bugging you about it so much.

 

[TCARPENTER]

Hi Mike,

This one isn't too hard, it looks like the routine can't find your table. Double check your spelling (I'm notorious for this one myself), and be sure it matches.

Don't worry about bugging me, you should see some of the stuff other people have helped me with! As far as books, everything I've done has come out of help files, so I'm afraid I wouldn't know what to reccommend.

HTH
Todd

 

[mcaraker]

YES! I had the table name right but decided to simplify it and get rid of the dash and it now works (at least back to how it worked before). So now I'm writing 141 blank rows to the table. Can I ask one last favor? This section needs to be modified to write out the first attribute in column "RMNO", the second in "ROOMNAME1" and the third in "ROOMNAME2" and I'm not sure how it should work. I thought what I have here would get me the first one but obviously not. How would I modify it to accomplish this?

For I = 1 To J
rstAttribs.AddNew
Set BLK = SS.Item(I)
varAttributes = BLK.GetAttributes
fldAttribs.Value = varAttributes(0).TextString
rstAttribs.Update
Next I

J is set to the number of instances of the selected block, so in this example I'm running through this 141 times to write out 141 rows of 3 fields each.

 

[TCARPENTER]

Hi Mike,

No problem, you can ask for as many favors as you need!

Define two new variables in your module that holds your for loop:

Dim strFld As String  ' String to hold recordset field name.
Dim iCnt   As String  ' Attribute counter.

Then change your for loop to:

For I = 1 to J
  Set BLK = SS.Item(I)
  varAttributes = BLK.GetAttributes
  For iCnt = LBound(varAttributes) to UBound(varAttributes)
    Select Case varAttributes(iCnt).TagString
      Case Is "RMNO"
        strFld = <<FieldName from Table for "RMNO">>
      Case Is "ROOMNAME1"
        strFld = <<FieldName from Table for "ROOMNAME1">>
      Case Is "ROOMNAME2"
        strFld = <<FieldName from Table for "ROOMNAME2">>
    End Select
    
    With rstAttribs
      .Addnew
      .Fields(strFld).Value = varAttributes(iCnt).TextString
      .Update
    End With
  Next ICnt
Next I

HTH
Todd

 

[mcaraker]

Sweet - i have data in my table now! However instead of one row per block i'm getting one row per attribute (so I have 423 rows instead of 141). I had to change iCnt to integer to get it to run, but haven't changed anything else yet from what you suggested. I'll play around with it a bit myself and see if I can't figure it out with logic and trial/error. Thanks again for your help!!

 

[TCARPENTER]

Hi Mike,

Glad it worked out for you, inspite of my goof (intCnt should have been an Integer type). Let me know if you need any more help!

Todd