topic Re: MDB data to Object Data in AutoCAD Map 3D Developer Forum

MDB data to Object Data

Anonymous — Mon, 04 Jul 2005 12:18:09 GMT

Hi all,

Do any one know how to get MDB data as object data on the basis of a link, with the fields available in the object data table?

Please let me know of any such possibilities.

Thanks,

MNRaghu

Re: MDB data to Object Data

Anonymous — Tue, 12 Jul 2005 17:54:34 GMT

We use OD to hold an ID that is used to retrieve data from an Access database. Is that what you are looking for? Or do you want to assign object data to an object automatically by connecting it to a database so the object data changes with the database changes?

Bart

Re: MDB data to Object Data

Anonymous — Wed, 13 Jul 2005 09:00:44 GMT

Bart,

>>>Or do you want to assign object data to an object automatically by connecting it to a database so the object data changes with the database changes?

Yes.This is my requirement.

Alternatively, is it possible that I have blank object data fields and fields by similar name in an MDB table.
Using a link, can I tranfer the values from MDB to Object data table?
Is this possible?

Thanks You,

MNRaghu

Re: MDB data to Object Data

Anonymous — Wed, 13 Jul 2005 13:38:48 GMT

I would think it very possible. It would require a degree of automation though. I use VBA code and the ADO connection object to connect my Maps to an Access database. I know that you can create a Link Template in MAP, but I don't know how that works, however there any number of people in the group who could help you with that.
I would think that you would need to assign a unique Key to each object that would be a record id in the database. Then you would need to write a routine that was either fired mannually by the user, called on startup or fired using a VB timer control, to connect to the Database and check for updates. I don't think that there is a way to link the drawing objects so that changes in the database happen as the affect the drawing objects instantly.
If you would like help with the ADO connection to the Database please let me know and I will be happy to share the code we use.

EDIT: To answer the other question, yes OD fields can be blank, and the fields in the database can be blank so long as the are not "required".

Bart Message was edited by: bart1123

Re: MDB data to Object Data

Anonymous — Thu, 14 Jul 2005 03:58:05 GMT

Hi Bart,

Thanks for your views.
I request you share the code the performs ADO connection to the Database which would help me to move a step ahead on this issue.

Thanks,

MNRaghu

Re: MDB data to Object Data

Anonymous — Thu, 14 Jul 2005 14:40:23 GMT

MNRaghu,
Below is the code that I use to open and close the ADO object to connect to the database. Following that is the code that prompts the user for a selection on screen, checks for Object Data and reads the OD if found. While this does not show how to write data from the database to the OD I hope that it is enough to give you a good primer on where to look. By the way this function was written entirely from the help files in Map on ActiveX autoation and API help.
I hope this helps you.

Bart
***************************BEGIN CODE***********************
'---------------------------------------------------------------------------------------
' Module : modDataBase
' DateTime : 6/3/2005 10:29
' Author : bart
' Purpose :This holds the Database connection string and deals with opening and _
closing the connection
'---------------------------------------------------------------------------------------
Option Explicit
Dim sConn As String
Public oConn As ADODB.Connection
'Make sure you have a reference in the project to MS Active Data Objects 2.x (I use 2.8)

Public Sub OpenDB
Set oConn = New ADODB.Connection

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyData\Database.mdb;" & _
"Persist Security Info=False"
oConn.Open sConn

Exit Sub
CheckError:
MsgBox "Database Connection Error" & vbCrLf & Err.Number & " - " & Err.Description
'LocErr = True
Exit Sub
Resume Next
End Sub

Public Sub CloseDB()
If oConn.State = 1 Then
oConn.Close
Set oConn = Nothing
End If

End Sub

The following code prompts the user to select an object with Object Data Attached, reads the value of the object data and querries the database for records matching the value of the Object Data

Public NEW_TLNUM As String
Public rsCable As ADODB.Recordset

Public Sub GetCableID()
Dim ODrcs As ODRecords

Dim boolVal As Boolean

Dim returnObj As AcadObject

Dim basePnt As Variant

Dim i As Integer
Dim answer As Variant
On Error GoTo errorhandler
'**********************************NEW****************
Dim amap As Object
Dim ODfdfs As ODFieldDefs
Dim ODfdf As ODFieldDef
Dim ODtb As ODTable
Dim ODrc As ODRecord

Set amap = ThisDrawing.Application. _
GetInterfaceObject("AutoCADMap.Application.2")
Set ODfdfs = amap.Projects(ThisDrawing).MapUtil.NewODFieldDefs

ThisDrawing.Utility.GetEntity returnObj, basePnt, "Select an object"
If returnObj.ObjectName = "AcDbBlockReference" Then
GetNodePic returnObj.Handle
Exit Sub
End If

' Add Column Headings and Defaults
Set ODfdf = ODfdfs.Add("CAB_ID", "Cab Disc", "23", 0)
Set ODfdf = ODfdfs.Add("Route_ID", "Rout Disc", "66", 1)

Set ODrcs = amap.Projects.Item(ThisDrawing). _
ODTables.Item("CABLE").GetODRecords

'Prompt user to select an object

boolVal = ODrcs.Init(returnObj, True, False)

For i = 0 To ODrcs.Record.Count - 1

NEW_TLNUM = ODrcs.Record.Item(i).Value
Next i

'*****************GET CABLE******************
Const conErrInvalidNull = 94
Const conErrNotObject = 438
Const conErrNoRecord = 3021

OpenDB 'open Connection

Set rsCable = New ADODB.Recordset
sSQL = "Select * from CABLE where CAB_ID = '" & NEW_TLNUM & "'" 'build SQL query to find record based on Cable ID

On Error GoTo errorhandler
rsCable.Open sSQL, oConn, , adLockOptimistic 'Finds record in Database based on CAB_ID

If Not rsCable.EOF Then 'If record is found then load record into form
Load frmCable
frmCable.Show
Exit Sub
End If

errorhandler:
Select Case Err
Case conErrNotObject
MsgBox ("This is not a cable."), vbExclamation
Case conErrInvalidNull
Resume Next
Case conErrNoRecord
Resume
Case Else
MsgBox ("Unexpected error :" & Err.Description), vbExclamation
End Select

End Sub
********************************END CODE************************

Re: MDB data to Object Data

Anonymous — Fri, 15 Jul 2005 03:44:28 GMT

Bart,

Thank a lot for sharing the code.

That should be more than enough for me to proceed.

Thanks again......

MNRaghu

Re: MDB data to Object Data

Anonymous — Wed, 20 Jul 2005 19:20:22 GMT

One way around customization is to export your data to a SHP - taking all the MDB data to SHP. Then import the SHP into a new DWG and have it create Object Data during the import.

Re: MDB data to Object Data

Anonymous — Thu, 21 Jul 2005 03:40:05 GMT

LochDhu,

That's a useful and good idea.

Thanks,

MNRaghu