AutoCAD Map 3D Forum
Welcome to Autodesk’s AutoCAD Map 3D Forums. Share your knowledge, ask questions, and explore popular AutoCAD Map 3D topics.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

ODBC Connectoin (Excel, Access)

17 REPLIES 17
Reply
Message 1 of 18
Anonymous
2229 Views, 17 Replies

ODBC Connectoin (Excel, Access)

Documentation for Map really sucks sometimes. I have spent a
ridiculous amount of time trying to accomplish something that should
be fairly simple.

One task was to establish an ODBC connection so that I could perform a
join. I had a lot of trouble, I searched: help, support, discussion
groups, and google.

Support did end up solving my problem trying to get the connection in
Access. It was:

Points from ODBC data source do not display in Autodesk Map® 3D
Published date: 2006-Jun-22
ID: TS1058108
Applies to:
Autodesk Map® 3D 2007
Issue
You have a Microsoft Access .mdb table that contains x and y values
that you want to display as points in Autodesk Map® 3D using the FDO
ODBC Provider. You were able to connect to the ODBC data source and
specify the required x/y columns in the Data Connect dialog box.
However, when you added data to the map, no point objects could be
found in the drawing even though the point layer appeared in the Task
pane of the Display Manager.
Solution
To display points from an ODBC table in Autodesk Map® 3D, the table
needs to have a primary key field defined in MS Access. This primary
key can be generated automatically or manually.

However, I have not been able to establish a successful Excel
connection. I get the table, but it is empty. I see this question a
few other places in the discussion group, but no solution has worked.
I thought "Cannot see contents of Microsoft® Excel spreadsheet in
Project Workspace" ID: TS34004 might do the trick, but it did not. I
am testing with a very simple worksheet. I do have the named cell
range. I have tried the extra rows cited in the mentioned support
document.

Terry S
17 REPLIES 17
Message 2 of 18
JohnPM
in reply to: Anonymous

Try this in the connection string and see if it works. Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path\file.xlsx

path is the absolute path of the source of your file. I am assuming you are using 2007



John
Message 3 of 18
Anonymous
in reply to: Anonymous

Terry,
Is the Excel you using from Office 2007?

--
Murph
www.map3d.wordpress.com


"Terry Scanlon" wrote in message
news:6089361@discussion.autodesk.com...
Documentation for Map really sucks sometimes. I have spent a
ridiculous amount of time trying to accomplish something that should
be fairly simple.

One task was to establish an ODBC connection so that I could perform a
join. I had a lot of trouble, I searched: help, support, discussion
groups, and google.

Support did end up solving my problem trying to get the connection in
Access. It was:

Points from ODBC data source do not display in Autodesk Map® 3D
Published date: 2006-Jun-22
ID: TS1058108
Applies to:
Autodesk Map® 3D 2007
Issue
You have a Microsoft Access .mdb table that contains x and y values
that you want to display as points in Autodesk Map® 3D using the FDO
ODBC Provider. You were able to connect to the ODBC data source and
specify the required x/y columns in the Data Connect dialog box.
However, when you added data to the map, no point objects could be
found in the drawing even though the point layer appeared in the Task
pane of the Display Manager.
Solution
To display points from an ODBC table in Autodesk Map® 3D, the table
needs to have a primary key field defined in MS Access. This primary
key can be generated automatically or manually.

However, I have not been able to establish a successful Excel
connection. I get the table, but it is empty. I see this question a
few other places in the discussion group, but no solution has worked.
I thought "Cannot see contents of Microsoft® Excel spreadsheet in
Project Workspace" ID: TS34004 might do the trick, but it did not. I
am testing with a very simple worksheet. I do have the named cell
range. I have tried the extra rows cited in the mentioned support
document.

Terry S
Message 4 of 18
Anonymous
in reply to: Anonymous

Sorry for the lack of info, folks. This is Map 3d 2008 and Excel
2003.

Murph wrote:
> Terry,
> Is the Excel you using from Office 2007?
>
> --
> Murph
> www.map3d.wordpress.com
>
>
> "Terry Scanlon" wrote in message
> news:6089361@discussion.autodesk.com...
> Documentation for Map really sucks sometimes. I have spent a
> ridiculous amount of time trying to accomplish something that should
> be fairly simple.
>
> One task was to establish an ODBC connection so that I could perform
> a
> join. I had a lot of trouble, I searched: help, support, discussion
> groups, and google.
>
> Support did end up solving my problem trying to get the connection
> in
> Access. It was:
>
> Points from ODBC data source do not display in Autodesk Map® 3D
> Published date: 2006-Jun-22
> ID: TS1058108
> Applies to:
> Autodesk Map® 3D 2007
> Issue
> You have a Microsoft Access .mdb table that contains x and y values
> that you want to display as points in Autodesk Map® 3D using the FDO
> ODBC Provider. You were able to connect to the ODBC data source and
> specify the required x/y columns in the Data Connect dialog box.
> However, when you added data to the map, no point objects could be
> found in the drawing even though the point layer appeared in the
> Task
> pane of the Display Manager.
> Solution
> To display points from an ODBC table in Autodesk Map® 3D, the table
> needs to have a primary key field defined in MS Access. This primary
> key can be generated automatically or manually.
>
> However, I have not been able to establish a successful Excel
> connection. I get the table, but it is empty. I see this question
> a
> few other places in the discussion group, but no solution has
> worked.
> I thought "Cannot see contents of Microsoft® Excel spreadsheet in
> Project Workspace" ID: TS34004 might do the trick, but it did not.
> I
> am testing with a very simple worksheet. I do have the named cell
> range. I have tried the extra rows cited in the mentioned support
> document.
>
> Terry S
Message 5 of 18
Anonymous
in reply to: Anonymous

JohnPM wrote:
> Try this in the connection string and see if it works.
> Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm,
> *.xlsb)};DBQ=path\file.xlsx path is the absolute path of the source
> of your file. I am assuming you are using 2007 John

Sorry for lack of info: I'm running Map 08 and Excel 2003.

I tried the path, and can't even get a connect. With the DSN method,
I get a connect...just no data.
Message 6 of 18
Anonymous
in reply to: Anonymous

Ok no problem.
When you connect to your name cell ranges in the FDO are you selecting the
columns for the x,y coordinates? You need to stretch the columns wider and
select the coulnm names for each.

Murph


"Terry Scanlon" wrote in message
news:6090204@discussion.autodesk.com...
Sorry for the lack of info, folks. This is Map 3d 2008 and Excel
2003.

Murph wrote:
> Terry,
> Is the Excel you using from Office 2007?
>
> --
> Murph
> www.map3d.wordpress.com
>
>
> "Terry Scanlon" wrote in message
> news:6089361@discussion.autodesk.com...
> Documentation for Map really sucks sometimes. I have spent a
> ridiculous amount of time trying to accomplish something that should
> be fairly simple.
>
> One task was to establish an ODBC connection so that I could perform
> a
> join. I had a lot of trouble, I searched: help, support, discussion
> groups, and google.
>
> Support did end up solving my problem trying to get the connection
> in
> Access. It was:
>
> Points from ODBC data source do not display in Autodesk Map® 3D
> Published date: 2006-Jun-22
> ID: TS1058108
> Applies to:
> Autodesk Map® 3D 2007
> Issue
> You have a Microsoft Access .mdb table that contains x and y values
> that you want to display as points in Autodesk Map® 3D using the FDO
> ODBC Provider. You were able to connect to the ODBC data source and
> specify the required x/y columns in the Data Connect dialog box.
> However, when you added data to the map, no point objects could be
> found in the drawing even though the point layer appeared in the
> Task
> pane of the Display Manager.
> Solution
> To display points from an ODBC table in Autodesk Map® 3D, the table
> needs to have a primary key field defined in MS Access. This primary
> key can be generated automatically or manually.
>
> However, I have not been able to establish a successful Excel
> connection. I get the table, but it is empty. I see this question
> a
> few other places in the discussion group, but no solution has
> worked.
> I thought "Cannot see contents of Microsoft® Excel spreadsheet in
> Project Workspace" ID: TS34004 might do the trick, but it did not.
> I
> am testing with a very simple worksheet. I do have the named cell
> range. I have tried the extra rows cited in the mentioned support
> document.
>
> Terry S
Message 7 of 18
Anonymous
in reply to: Anonymous

Hi Murph,

I had actaully read previous posts of yours where you mentioned this
and tried to do something similar to what you describe with no
results. This is actually an non-spatial table.

Here is an explanation of what I was trying to do:
I already have a connection to an SDF which includes a certain data
field (Soil Types)
I have a spreadsheet which correlates the soil type to hydrologic soil
groups A, B, C, or D. (non-spatial)
I wish to create a link between the data sets in order to map
hydrologic soil groups.

(I have actually accomplished this by bringing the Excel data into an
Access database and have succussfully used ODBC to connect to that
data source. I am just stuck on making this work with Excel as well.
Not everyone has Access. )

Terry

"Murph" wrote in message
news:6090444@discussion.autodesk.com...
Ok no problem.
When you connect to your name cell ranges in the FDO are you selecting
the
columns for the x,y coordinates? You need to stretch the columns wider
and
select the coulnm names for each.

Murph


"Terry Scanlon" wrote in message
news:6090204@discussion.autodesk.com...
Sorry for the lack of info, folks. This is Map 3d 2008 and Excel
2003.

Murph wrote:
> Terry,
> Is the Excel you using from Office 2007?
>
> --
> Murph
> www.map3d.wordpress.com
>
>
> "Terry Scanlon" wrote in message
> news:6089361@discussion.autodesk.com...
> Documentation for Map really sucks sometimes. I have spent a
> ridiculous amount of time trying to accomplish something that should
> be fairly simple.
>
> One task was to establish an ODBC connection so that I could perform
> a
> join. I had a lot of trouble, I searched: help, support, discussion
> groups, and google.
>
> Support did end up solving my problem trying to get the connection
> in
> Access. It was:
>
> Points from ODBC data source do not display in Autodesk Map® 3D
> Published date: 2006-Jun-22
> ID: TS1058108
> Applies to:
> Autodesk Map® 3D 2007
> Issue
> You have a Microsoft Access .mdb table that contains x and y values
> that you want to display as points in Autodesk Map® 3D using the FDO
> ODBC Provider. You were able to connect to the ODBC data source and
> specify the required x/y columns in the Data Connect dialog box.
> However, when you added data to the map, no point objects could be
> found in the drawing even though the point layer appeared in the
> Task
> pane of the Display Manager.
> Solution
> To display points from an ODBC table in Autodesk Map® 3D, the table
> needs to have a primary key field defined in MS Access. This primary
> key can be generated automatically or manually.
>
> However, I have not been able to establish a successful Excel
> connection. I get the table, but it is empty. I see this question
> a
> few other places in the discussion group, but no solution has
> worked.
> I thought "Cannot see contents of Microsoft® Excel spreadsheet in
> Project Workspace" ID: TS34004 might do the trick, but it did not.
> I
> am testing with a very simple worksheet. I do have the named cell
> range. I have tried the extra rows cited in the mentioned support
> document.
>
> Terry S
Message 8 of 18
Anonymous
in reply to: Anonymous

Ok Got ya,
I just tried this and get the same error on a simple speadsheet too, using
Map3D 2009 and Excel 2003.

Murph


"Terry Scanlon" wrote in message
news:6090754@discussion.autodesk.com...
Hi Murph,

I had actaully read previous posts of yours where you mentioned this
and tried to do something similar to what you describe with no
results. This is actually an non-spatial table.

Here is an explanation of what I was trying to do:
I already have a connection to an SDF which includes a certain data
field (Soil Types)
I have a spreadsheet which correlates the soil type to hydrologic soil
groups A, B, C, or D. (non-spatial)
I wish to create a link between the data sets in order to map
hydrologic soil groups.

(I have actually accomplished this by bringing the Excel data into an
Access database and have succussfully used ODBC to connect to that
data source. I am just stuck on making this work with Excel as well.
Not everyone has Access. )

Terry

"Murph" wrote in message
news:6090444@discussion.autodesk.com...
Ok no problem.
When you connect to your name cell ranges in the FDO are you selecting
the
columns for the x,y coordinates? You need to stretch the columns wider
and
select the coulnm names for each.

Murph


"Terry Scanlon" wrote in message
news:6090204@discussion.autodesk.com...
Sorry for the lack of info, folks. This is Map 3d 2008 and Excel
2003.

Murph wrote:
> Terry,
> Is the Excel you using from Office 2007?
>
> --
> Murph
> www.map3d.wordpress.com
>
>
> "Terry Scanlon" wrote in message
> news:6089361@discussion.autodesk.com...
> Documentation for Map really sucks sometimes. I have spent a
> ridiculous amount of time trying to accomplish something that should
> be fairly simple.
>
> One task was to establish an ODBC connection so that I could perform
> a
> join. I had a lot of trouble, I searched: help, support, discussion
> groups, and google.
>
> Support did end up solving my problem trying to get the connection
> in
> Access. It was:
>
> Points from ODBC data source do not display in Autodesk Map® 3D
> Published date: 2006-Jun-22
> ID: TS1058108
> Applies to:
> Autodesk Map® 3D 2007
> Issue
> You have a Microsoft Access .mdb table that contains x and y values
> that you want to display as points in Autodesk Map® 3D using the FDO
> ODBC Provider. You were able to connect to the ODBC data source and
> specify the required x/y columns in the Data Connect dialog box.
> However, when you added data to the map, no point objects could be
> found in the drawing even though the point layer appeared in the
> Task
> pane of the Display Manager.
> Solution
> To display points from an ODBC table in Autodesk Map® 3D, the table
> needs to have a primary key field defined in MS Access. This primary
> key can be generated automatically or manually.
>
> However, I have not been able to establish a successful Excel
> connection. I get the table, but it is empty. I see this question
> a
> few other places in the discussion group, but no solution has
> worked.
> I thought "Cannot see contents of Microsoft® Excel spreadsheet in
> Project Workspace" ID: TS34004 might do the trick, but it did not.
> I
> am testing with a very simple worksheet. I do have the named cell
> range. I have tried the extra rows cited in the mentioned support
> document.
>
> Terry S
Message 9 of 18
ckrismer
in reply to: Anonymous

I have a mdb file that I am trying to connect to.  I am a newbie.  When I choose the headers for the x and y columns, I don't know what to select.  The available headings include such items as FOREST_ID, HECTARES, etc.  Adding headers at random brings features into the map, but not the correct shapes.  (Using connect to data / add ODBC).  I appreciate any help offered.

Message 10 of 18
Murph_Map
in reply to: ckrismer

The shapes displayed are the default you need to style them after you add the features to the map. As for the X-Y column use the columns that have the X corrdinate and Y coordinate.

Murph
Supporting the troops daily.
Message 11 of 18
ckrismer
in reply to: Murph_Map

Hey Murph,

 

I appreciate your willingness to help, but the solution you proposed does not seem applicable.  This is a municipally provided mdb file. Using Auto Cad Map 3D 2011 - first time map user. I am seeing little squares to indicate forest, but they aren't in the right place and they are just squares, not drawn shapes.  (The orthographic photos come in fine.)

My database does not contain x,y coordinates. There is a bioforest file and a bioforest shape index, for example. The column names I can choose for bioforest are: OBJECTID CRE_DATE CRE_BY GEO_MODDATE GEO_MODBY ATT_MODDATE ATT_MODBY CAP_METHOD FOREST_ID CLASS HECTARES ACRES SHAPE_Length SHAPE_Area.  The shape index has these headings MinGX MinGY MaxGX MaxGY.

 

On a differnt forum site someone said:

"Consult your AutoCAD Help files specifically the topic database records, accessing. When the first window appears three topics will be listed. Take a look at the first one called Configure a database for use with AutoCAD drawings.

"Note: The 64-bit release of AutoCAD does not support the use of Microsoft Jet 4.0 OLE DB Provider (for .MDB connectivity) and Microsoft OLE DB Provider for ODBC Drivers (for .XLS connectivity). For more information, see Substituting SQL Server for OLE DB."

 

I'm going to investigate the OLE DB option.  I had no success trying to create an SQL Server.

Message 12 of 18
AllenJessup
in reply to: ckrismer

Is this possibly a Personal GeoDataBase file? The SHAPE_Length SHAPE_Area names suggest that it might be. Is there a web link so we could download and test it?

 

Allen



Allen Jessup
Engineering Specialist / CAD Manager

Message 13 of 18
ckrismer
in reply to: ckrismer

I can't give you the actual data (some kind of confidentiality agreement), but it is an mdb file based on the data used to create the maps below.  Most of the features do not have x,y values - two features do - street trees and illumination,   There are no shp files just the mdb.

 

http://maps.york.ca/yorkexplorer/default.jsp

 

Thanks

Message 14 of 18
Murph_Map
in reply to: ckrismer

A odbc connection will only allow you to display point type objects, if you bioforest is a polygon type features then Map3D will not render any shapes for you.

I agree with Allen sounds like you have an E$RI Personal Geodatabase not a true MS Office Access database.

Murph
Supporting the troops daily.
Message 15 of 18
ckrismer
in reply to: Murph_Map

Groovy.  Thanks guys.  I'll have to ask for the data in a different format.

Message 16 of 18
AllenJessup
in reply to: ckrismer

Yeah. Almost certainly an ESRI Geodatabase. Since you can access it, it's most likely an Personal Geodatabase. Search the group for FDO & OGR. There are quite a few post on this. Most of them answered by Murph. Also a web search will yield a lot.

 

The squares you see are probably unstyled points. In order to see the polygons you need to create an OGR connection. This is the site you'll need to download the files you'll need: http://fdo.osgeo.org/content/downloads

 

Also see Using Open Source FDO Providers in Help. And:http://images.autodesk.com/adsk/files/fdo_data_access_technology_third_party_providers_final.pdf

 

Allen



Allen Jessup
Engineering Specialist / CAD Manager

Message 17 of 18
AllenJessup
in reply to: ckrismer

See my post above. It's definitely worth learning about FDO connections. They're a very powerful tool for viewing GIS data. [Editing it too, but I don't get in to that.]

 

Allen



Allen Jessup
Engineering Specialist / CAD Manager

Message 18 of 18
ckrismer
in reply to: AllenJessup

I'm working on it - thanks.  Robot Happy

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report

”Boost