32bit AND 64bit environment database connectivity

[Anonymous]

Hi All!

I am interested in getting some dialog going on this subject.  Experts, PLEASE chime in!!!

I understand that basic non COM applications compiled in VS using "Any CPU" as the target CPU will run in both 32bit and 64bit. At least in AutoCAD 2011 and above. I also understand that the MS Jet connector will not run in 64bit mode.

So... I (among others) need some advice when it comes to working with database connections in BOTH 32bit and 64bit.  I am wondering if we can get some dialog going to provide a complete and accurate solution for the many of us that have (or will have) these requirements.

I have searched but cannot find any complete solutions in the forums.  Surely some of you .NET wizards have already resolved this issue!

My personal requirement is this: We are an all 32bit (XP) office with plans to upgrade every machine to 64bit (Win 7) over time.  The first 64 bit machine will be rolled out in less than two weeks.  It will take us the better part of two years to completely upgrade all of the office machines. Obviously we will need to have custom .NET applications running long before the computer upgrade process is complete.

I have an Access database that I need simple connectivity to inside of AutoCAD. I am basically populating controls with contents from certain fields in the tables of the database. I do not need to make any changes to the database from AutoCAD (at this point in time).  The Access database will be used by others outside of AutoCAD.  The database will be updated on a regular basis by users outside of AutoCAD, so I am not sure if changing from Access is an option.

So... what is the BEST and most SCALEABLE solution for connecting to a database that will be manipulated by outside users while maintaining the ability to work on both 32bit and 64bit machines?

I have read a few different things in these forums; suggestions of using SQL Express, XML, Access 64bit, etc... But it's all just a bit confusing to me. It would be nice to put this to bed.

TIA!

[norman.yuan]

Your issue is mainly regarding using Access database (*.mdb/*.accdb) with 64-bit OS and 64-bit AutoCAD, not a generic "database". If you use a server type database, such as SQL Server, MySQL, you would not have this issue.

There a few things you need to know:

There are two type of Access database since the release of MS Access2007/MS Office 2007. All Windows OS come with MS Jet Engine installed, which the component that provides access to data stored in MS Access database  (*.mdb file) until MS Access 2007. That is, one can write applications that access data stored in *.mdb file without installing MS Access application, because the application uses DAO/ADO in conjunction with Jet Engine to access/query data in *.mdb file.

Then, starting from MS Access 2007, a new database engine is used (MS Access tabase engine), instead of Jet Engine. Originally, this database engine only comes with MS Access installation, meaing, you cannot access data in the *.accdb file unless you install MS Access application. This caused outcry of course, and later MS made MS Access Engine (for MS Access database 2007 and later) a free download, so the data in *.accdb then can be manipulated by custom apps as in *.mdb. However, the new database engine must be explicitly downloaded in installed, if the computer does not have MS Access installed.

Also, The free download of MS Access database engine is back-compatible, meaning it can be used to access not only database created by MS Access2007 (*.accdb), but also earlier Access version (*.mdb). The same as Jet Engine, it can also be sued to access data stored in Excel sheet without opeing it in Excel application.

Now, as you ca see, data in access databse, can be manipulated within MS Access or outside MS Access (custom apps, including apps for AutoCAD) as long as the MS Access database engine works in the application environment.

Then it comes the issue of 32-bit or 64-bit (of MS Access database engine).

When you need to access data in *.mdb/*.accdb with 64-bit AutoCAD, the MS Access database engine must be 64-bit, which wasn't available originally until resently. So, yes, you can write application, be it COM based (or VBA), or .NET based to access data in *.mdb/*.accdb for 64-bit Acad, as long as the running computer has 64-bit MS Access database engine installed. The controversial part is, most computer users may have 32-bit MS office installed in the 64-bit Windows (as MS recommended), which would prevent 64-bit MS Access database engine being installed.

That is, if the user has 32-bit MS Office installed, and the applications they use to manipulate the data in the said Access database are mostly 32-bit (be it MS Access itself, or custom apps), then it is likely the 64-bit MS Access database engine cannot be installed in the same box, thus your 64-bit AutoCAD cannot access the data in the *.mdb/*/accdb. Or I say the other way around, if you installed 64-bit MS Access database engine in order for your Acad to work with the data, then all other apps in the box need to be the 64-bit apps in order to access the data with the shared 64-bit Access database engine.

However, if your data is stored in a server type database (SQL Server...), then you can access the data from either 32-bit apps or 64-bit apps. Or if the data is in plain text file, or XML file, then there is not such complicated issue as it with MS Access database.

Hope this help a bit.

[Anonymous]

Norman... this helps GREATLY!!!

Thanks sooo much for the explanation!  I think others will find this info VERY helpful as well!

When the new 64bit machine rolls in here in a couple of weeks, I am going to start testing this out.  I may be asking you a few more questions then. 😉

Thanks again!

[Anonymous]

And hopefully you will be using SQL Compact server to solve these platform problems.

Of course the SQL Compact query language is slightly differently constructed than what you use in the other SQL products. But what do you expect for a free, low imact db ?

[Anonymous]

Based on the info Norman provided and some other reading I have been doing, we have decided to go the SQL route rather than deal with the potential pitfalls of Access.

So... after doing some light reading on SQL, I need help determining what version/process would work best for our needs.

Here is a slightly more detailed explanation of what we need to accomplish.  All of this is currently being done in VBA and Access.  I have written new .NET code that also accomplishes most of this, but before we get too far down the road with the new .NET code, we want to be sure we are using the right tools for the job!

We have an Access database that resides on our server.  This database maintains hundreds of parts and part details, such as size, weight, price, etc... (in several tables)   The accountants in the office edit this database regularly (adding new parts, changing pricing, etc...)  As of now, they are doing the editing in Access.  Eventually we would like to create a .NET interface for them, but that is a ways down the road (but could have an impact in this database decision).

This database needs to be connected to a .NET routine that runs inside of AutoCAD so users can select certain parts/part details from controls in the application.  Those values will then be pushed into attribute values of blocks, textstrings, etc... in our drawings.

For this particular application, will will only be reading the database and running querries on it, but future AutoCAD/.NET applications will require the ability to write to the database.

The majority of our users are local (all in the same office), but we do have a few users that work in a seperate office that is connected via WAN.  We also have a couple of consultants that work from home and connect via VPN.

Based on the above info, can someone help us make an intelligent choice as to what version(s) of SQL we should pursue?  Based on the reading I have done, it sounds like SQL Server Compact Edition can supprt this, but I want to be sure!

TIA!

[norman.yuan]

Make sure you know the difference between SQL Server Compact and SQL Server (Express/Standard/Enterprise). The issue you orginally have is because of using MS Access database and 64-bit AutoCAD.

SQL Server Compact, inspite of its name "Sql Server", is a file based database, and is meant for single user using in a mobile device/laptop, although you can set it up in a network share for multiple users to share the data. IMO, it would not be suitable for your situation. You either stick with MS Access database, or go for a server based database server (then SQL Server is the natural choice). For the former, you either go one step backward to use 32-bit AutoCAD, or install 64-bit MS Acccess database engine (then probably MS Office has to be 64-bit, other apps run in the same PC that accesses the same data would have to be 64-bit). So, the viable and long term solution would be the latter: moving to SQL Server.

Consider following factors:

1. For a small/medium size office, you can start with SQL Server Express 2008 with Advanced Features (e,g. including Reporting Services, which is very useful feature). It is free with some setting up limitations, such single CPU, 4G database size. Its 4G database size is usually more than enough to get start with. In future, if the data load goes up, simply move to Standard/Enterprise version without application/code change at all.

2. After moving your existing data in *.mdb/*.accdb file into a SQL Server database, the UI in the MS Access can be preserved, or with minimum changes. I assume your current MS Access app is well developed as front end and back end. That is, user runs MS Access app as front end that contains forms/queries/reports and connects to a back end *.mdb/*.accdb file containing tables only. So, it is very likely with minimum modification, the MS Access front end can connect to the SQL Server database and to users, they still use the same tool to work on data in the database. This would be very critical for your transition. But you need to find someone, who really knows how to programming MS Access front end, which is significantly different from VB6/VBA/.NET programming., to do the minior change to your existing MS Access front end app.

3. Using server based database would be beneficial to WAN users, because the query happens in the server side and only the query result is send to the client, while the file based DB, such as MS Access DB, query happens in the client side, so in most cases a lot more data has to be send to client end before a query can find the needed data.

4. There are tons of resources available on line for programming .NET with SQL Server. So, you would have much less technical difficulties lying ahead of you.

HTH

[Anonymous]

Hello, I was very happy to find this info above.

I have a x64 windows and an x64 AutoCAD installed. I read that I had to installed the x64 Office aswell to connect my Access database so the error would not appear anymore when debugging my VB.NET project.

But it still shows up when making the connection to an Access database.

What I read now is that because Visual Studio is an 32 bit application, all the others you are connecting to (AutoCAD, Access) should be 32 bit aswell.  So this whole 64-bit database engine thing is only for already build applications, not for the ones who are actually developing the app 😞

Can anyone confirm what I read on some other forum?

[Anonymous]

I have not a clue as to what the BEST solution may be. IMHO the most SCALABLE, the wisest, the longest term and most versatile solution is to completely remove ALL _external_database functionality from AutoCAD. Develop the tools and techniques to communicate between AutoCAD and an external application via IPC(inter process communication). I, for example, use Windows Communication Foundation. When it has made sense I can and have used multiple machines to extract object and geometric information from an AutoCAD database. .NET datasets/datatables are totally serializable and coupled with WCF _net.tcp://_ you can put together a very robust infrastructure. The issue of 32bit vs. 64bit apps becomes an almost non-issue. AutoCAD versions become almost a non-issue. The database you use becomes almost a non-issue.

r,

dennis

[Anonymous]

In the end I found a solution that worked for me, put the platform in the compiler on x64 aswell, not "any CPU".

[Rob.O]

Unfortuantely I don't think that is a viable solution for those of us that are still in a mixed 32bit and 64bit environment! 😞

[Anonymous]

You are right. Mine got accidently working after I compiled the project on a 32-bit computer.  It was only working after the build. Inside VisualStudio itself I could not connect to the databse.  But now I did found the solution.

Installing the "Microsoft Access Database Engine 2010 Redistributable", followed by the "2007 Office System Driver: Data Connectivity Components" solved the problem for me!

When VisualStudio is debugging it wants the 32-bit version of the ACE.OLEDB.12.0 provider, so you need both of them on your computer.  When you stop debugging you get back into VisualStudio and the Database will look like disconnected again. Just refresh and he finds the right OLEDB provider. Some said it is not possible, but it does!

It's written on the forum below what steps you have to follow.

http://social.msdn.microsoft.com/Forums/en-NZ/sqldataaccess/thread/c371fb35-6bdd-42e6-b88e-d1c0003d2...

[Anonymous]

I agree with norman - go to SQL Server ( but add a little bit of ADO.NET for completness ).

We have lots of DB-Acad-.XSLx-CSV-and-you-name-it work plus x86 and x64 AutoCADs (even 32bit Acad on 64bit comp.) on different machines plus legacy code (!). If you add to the .NET-SQL tandem a little bit of ADO.NET 4 you are on the right direction.

From ADO.NET we get data from DWGs then we sort and consolidate and then we export to whatever we want ( ADO.NET has data providers for almost everything).

My choice after working for some years with MySQL+ODBC+ACAD is MSSQL+.NET+ADO.NET+OpenXML+ACAD.

Explore this link http://msdn.microsoft.com/en-us/library/ms971485.aspx for ADO.NET to Access.

[Anonymous]

I haven't read all the postings in detail, but I found a way to install 64Bit ACE Engine on systems with 32 Bit Office.

Download AccessDatabaseEngine_x64.exe http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255 

And install it via Command Line with:

> AccessDatabaseEngine_x64.exe \passive

Now you should be able to connect to Access DB Files even though it is 32 Bit and your environment is 64 Bit.

[MRiemenCAD]

Just wanted to let you know that your repsonce here was very helpful to me too. I've been struggling with a suite of Access databases used over the past 10 years and now moving to .net to connect both 32bit and 64bit Acad to them. It's time to update the DB's before i can move forward. Again, appreicate your thoughtfull responce.

cheers.

[Anonymous]

Has anyone found a solution and tell me how to do? I have read hundreds of pages but have nothing.

I want to read excel files with the connection string        

connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                       "Data Source=" & FileName & ";" & _
                       "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"""

I am using Windows 7 64 bit, Autocad 2012 64 bit, Office 2010 32 bit.

If not an Autocad dll, I can change target cpu to X86 and it works, but for Autocad it has to be X64, and I can't install 64 bit Jet since Office is 32 bit. There is no way i can do this.

[norman.yuan]

Although you said "it is no way I can do this", have you tried to install MS Access Engine 64blt, as suggested by plonga's reply? AFAIK, one can install 64-bit MS Access Engine with MS Office 2010 32-bit in the same computer (it is not possible if you use MS Office 2007). But you may have to uninstall MS Office first, then install the MS Access Engine 64-bit and reinstall MS Office 32-bit. You may read more on this topic by searching the web. Bottom line is, for your code shown here to work, you must install MS Access engine 64-bit, because your AutoCAD is 64-bit. It can be done. But I'd not suggest it is good solution in most cases. Read on.

On the other hand you can easily to have a solution (maybe a better sotion for your specific need) to get around the 64-bit MS Access Engine issue:

•  Do you really need to store data in Excel worksheet format? If the *.xlsx file is only for storing data, not the presentation format, while you still prefer to be able to open the data file in Excel, you can consider to save the data as *.csv format, which is comma delimited text data string and can be read/written from/to by code very fast, and can be open in Excel.
•  If you want to keep the presention format in Excel, you can consider to use Open Xml, which is designed to access MS Office document (*.docx, *.xlsx) without the need of installing MS Office (Word, Excel), to access data stored in *.xlsx file.
•  if the data is shared office-wide/by multiple users, consider to place data in a sutale database system (SQL Server, SQL Server Express...). Just the facotr that you need to install MS Acess Engine 64-bit in every computer that may run your code along may lead you to give it up.

[jaboone]

I have been forced to research SQL server lately since I have a database Licensing program I am trying to work with.  I am having a hell of a time wrapping my head around how to set up a server for a specific file so it can be accessed from an external program.  It happens to be named LicenseService.mdb  My program has to add and manipulate data within that file, which happens to be on the internet.

Since it is named as an mdb, does this mean that SQl can not open or manipulate it?  Does simply installing MS SQL automatically set up my server, since my licensing program has to make a SQL command.  I don't understand how this whole thing  works.

When setting up a "server", How do I do that and what would those names be?

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

I investigated servers but don't know how to set them up for calling connections strings.  Not sure I have a 'server'.

Is the configuration manager on the right track or do I not get the picture?

[norman.yuan]

*.MDF file is just data file holding data for particular database hosted by a SQL Server. your application/code never deals with it (except for attaching it to a SQL Server, if the code is for SQL Server database setup).

For your applicatioon (AutoCAD addin, right?) to access data in a database hosted by a SQL Server, you need to have a SQL Server and a database hosted iin the SQL Server reachable in the network (intranet, usually, or the same computer). If the SQL Server is hosted on the Internet, well, technically, it can be allowed to be accessed to directly via the Internet, but no one in his right mind will open the database's door wide to the Internet. To assess data via the Internet, you need to build a web application and/or a web service application, and hide the SQL Server/database behind (e.g. the web application/services will be accessing the database when your user uses the web application/services). It is quite complicated when the Internet is involved.

If your users live inside a corporate network, you can then have your AutoCAD addins dirrectly access the SQL Server/database, although a middle layer of data access would be the best practice.

My feel is, if your solutioon is baed on data hosted in the Internet, you need to find help from someone who knows how to do web applications/services, or you need to learn a lot before doing the solution. Never try to connect SQL Server via the Internet (you'll never get through the connection, unless you host it yourself and make it wide open to the net).

Or, you may start with installing/hosting SQL Server in the LAN or your own computer and learn more. When you are comfortable with data access technology with SQL Server (or any other database server, foe that matter), you can then look into moving the data into the web. I am sure you know SQL Server Express is free. you can install it in your computer and start from there.

[dgorsman]

Pardon if you already know all this, if so maybe it will help others moving to the deep end of the pool.

SQL Server doesn't use "files" per se, at least not how Access or SQLite uses them.  You run SQL Server on a server, which then allows for the creation of databases and contained tables, queries, and other database-related information.  When establishing a connection, its similar to conneccting to an Access database (file), except instead of specifying a file you specify a server which is running the SQL Server program.

SQL Server set up and management is a very complex topic, to the point where its practical for people to specialize in nothing *but* that.  If you don't have one of those specialists on staff, bringing in a consultant will be worth the cost.

[jaboone]

I am with you and thanks for your very usefull information.  I am new to accessing data, but the applications I have builds a web application (ASP) which I have to password protect beyond a point.  Yes the datafile is hidden from direct access, but your feedback tells me I have to look closer at the hosting service to get them to provide a sql server so I can write to the database, I believe is what I am hearing, instead on my local machine.

Thanks very much for the explainattion.