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.
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.
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.
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 ?
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!
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.
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?
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.