Problem with Microsoft.Jet.OLEDB.4.0

[btmcad]

I created a program that sends info to an Access database. The connection string is:
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=R:\CAD\SUPPORT\RD_CAD_LOG.mdb;User Id=admin;Password=;"

This works fine for my system running Windows XP and Office 2003. However, I recently loaded my system on a 64 bit system running Windows 7 and office 2010.

When I run the program, I get an unhandled exception: "The Microsoft.jet.OLEDB.4.0 provider is not registered on this machine."

Is there a Jet 4.0 that will work with the above mentioned system?

Thank you
btmcad

[jmcilhinney]

There is no 64-bit version of Jet and, if your project's target platform is set to Any CPU then it will run in 64-bit mode on 64-bit systems. You must change the target platform to x86 so that it runs in 32-bit mode on all systems.

[btmcad]

Does this method still work well on a 64 bit system when set to X86?
Does it slow it down considerably?

Is there a better solution than the Jet driver for 64 bit systems.


Thanks for your input.
btmcad

[kareninstructor]

Under normal conditions you will not see any difference in speed. You should consider another option of moving to MS-SQL Server database which is much better in many ways over MS-Access.

[btmcad]

I tried changing from AnyCPU to X86. Unfortunately, this creates an error in the program that the info is being transferred from. In my program, info is exported from AutoCAD 2012 into Access 2010. With the X86 setting, my program will not load in AutoCAD.

My program worked fine in when I was running it on an XP 32 bit system and Access 2003. So, I'm trying to find a method to make this work properly with Windows 7 (64 bit) and Access 2010.

I did some research, and found the Microsoft offers the ACE driver for Access 2010. I haven't tried this yet, but I'm wondering how well it works, and if it's the right answer for getting my application working quickly.

I'm also researching SQL Server Express. I know very little about it yet.

Any input would be greatly appreciated.


Thanks
btmcad

[Shaggy Hiker]

If SQL Server is an option, then go with that.

Other than that, what has been said should work. What is the error you are getting with AutoCAD?

I remember looking at ACE when I first encountered this problem, but found that switching to x86 was simpler. I forget the reason. I think there were some kind of limitations with using ACE that I didn't want to deal with.

[kareninstructor]

Take a look at Microsoft Access Database Engine 2010 Redistributable for working with your Access database.

[Jack Tomy]

i had your same problem, i searched for the solution for weeks but it was clear that i had to change my db type!!

after updating windows xp 32bits to vista 64bits, im using Data.SlqClient.Sqlconnection.
I wont say that it wasnt hard at the beginning, but now its more better !

[jmcilhinney]

I tried changing from AnyCPU to X86. Unfortunately, this creates an error in the program that the info is being transferred from. In my program, info is exported from AutoCAD 2012 into Access 2010. With the X86 setting, my program will not load in AutoCAD.

My program worked fine in when I was running it on an XP 32 bit system and Access 2003. So, I'm trying to find a method to make this work properly with Windows 7 (64 bit) and Access 2010.

I did some research, and found the Microsoft offers the ACE driver for Access 2010. I haven't tried this yet, but I'm wondering how well it works, and if it's the right answer for getting my application working quickly.

I'm also researching SQL Server Express. I know very little about it yet.

Any input would be greatly appreciated.


Thanks
btmcad

It sounds like AutoCAD is 64-bit on 64-bit systems. By making your app 32-bit you end up with basically the same problem as before. Basically, if you need to use 32-bit AutoCAD on 32-bit systems and 64-bit AutoCAD on 64-bit systems then you need to use a database that behaves the same way. You could use the Access ACE provider instead of Jet as it comes in 32-bit and 64-bit flavours, but that won't work if someone already has 32-bit Office installed because you can't install both on the same system. SQL Server Express or SQL Server CE are probably your best options. Both are free, both integrate well with VS and both are available in both 32-bit and 64-bit flavours. SQL Server Express requires installation while SQL Server CE can be installed or just distributed with your app.

[btmcad]

So, I tried the "ACE" driver, but ended up with the same error message that the original JET driver gave me.

Yes, the AutoCAD is typically 64 bit on a 64 bit system. I beleive you can install it as a 32 bit on a 64 bit system, but in the office I'm working in, it's installed as 64 bit, and I prefer not to change it, because that will open up other issues.
The ms office software is 32 bit.

My original program, using MS Access was handy, because if the users needed to view the database for any reason, they could just open the MS Access software and easily view it.

If I switch to an SQL Server database, I could probably easily create a window in VB.net that would act as a viewer for the database.

I'm very new to database work, so I need some suggestions on how to switch over to the SQL Server.

Below is the main code that I used to create the connection and the insert / update commands for the the MS Access database.

conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\CAD\SUPPORT\RD_CAD_LOG.mdb;User Id=admin;Password=;"
        da.SelectCommand = New OleDbCommand("SELECT ID, Dwg_Number, Revision, Part_Number, Date_Created, Drawn_By, Description, Location FROM rd_cad_log")
        da.SelectCommand.Connection = conn

        da.UpdateCommand = New OleDbCommand("UPDATE rd_cad_log SET Dwg_Number = @Dwg_Number, Revision = @Revision, Part_Number = @Part_Number, Date_Created = @Date_Created, Drawn_By = @Drawn_By, Description = @Description, Location = @Location WHERE ID = @ID")
        da.UpdateCommand.Connection = conn
        da.UpdateCommand.Parameters.Add("@Dwg_Number", OleDbType.VarChar, 50, "Dwg_Number")
        da.UpdateCommand.Parameters.Add("@Revision", OleDbType.VarChar, 255, "Revision")
        da.UpdateCommand.Parameters.Add("@Part_Number", OleDbType.VarChar, 255, "Part_Number")
        da.UpdateCommand.Parameters.Add("@Date_Created", OleDbType.VarChar, 255, "Date_Created")
        da.UpdateCommand.Parameters.Add("@Drawn_By", OleDbType.VarChar, 255, "Drawn_By")
        da.UpdateCommand.Parameters.Add("@Description", OleDbType.VarChar, 255, "Description")
        da.UpdateCommand.Parameters.Add("@Location", OleDbType.VarChar, 255, "Location")
        da.UpdateCommand.Parameters.Add("@ID", OleDbType.Integer, 5, "ID") '.SourceVersion = DataRowVersion.Original


        da.InsertCommand = New OleDbCommand("INSERT INTO rd_cad_log(Dwg_Number,Revision, Part_Number, Date_Created, Drawn_By, Description, Location) VALUES(@Dwg_Number,@Revision,@Part_Number,@Date_Created,@Drawn_By,@Description,@Location)")
        da.InsertCommand.Connection = conn
        da.InsertCommand.Parameters.Add("@Dwg_Number", OleDbType.VarChar, 50, "Dwg_Number")
        da.InsertCommand.Parameters.Add("@Revision", OleDbType.VarChar, 50, "Revision")
        da.InsertCommand.Parameters.Add("@Part_Number", OleDbType.VarChar, 50, "Part_Number")
        da.InsertCommand.Parameters.Add("@Date_Created", OleDbType.VarChar, 50, "Date_Created")
        da.InsertCommand.Parameters.Add("@Drawn_By", OleDbType.VarChar, 50, "Drawn_By")
        da.InsertCommand.Parameters.Add("@Description", OleDbType.VarChar, 50, "Description")
        da.InsertCommand.Parameters.Add("@Location", OleDbType.VarChar, 50, "Location")
        da.Fill(ds)

So, if someone could point me in the correct direction, I would greatly appreciate it.

This program is used by approx 20 draftsmen / engineers in an R&D office. I have the source path above set to C:\... just for testing from my hard drive, but typically, the database would reside on a server.


Thanks!
btmcad

[jmcilhinney]

Once you have the database in place, using SQL Server in your app is pretty much exactly the same as using Access. You simply switch all your OleDb types, e.g. OleDbConnection, to the equivalent SqlClient types, e.g. SqlConnection. As for viewing the database, you can install SQL Server Management Studio Express if you want, which is free. That's what you'll be using to create the database in the first place, unless you want it to be local to your app specifically, in which case you'll use VS.

[btmcad]

I have downloaded the MS SQL Server Express and the MS SQL Management Studio.

So far, I've found both of these to be very unintuitive, but that may just be my inexperience with database systems.

To create my database, I first need to take my existing Access database and convert it to the MS SQL. I've tried the Import / Export tool with no success.
I've followed some online tutorials for Import feature, but I constantly receive an error message stating that it can't connect with the server. I've tried everything that I can think of. I beleive I have the server name correct.

For testing purposes, the database is currently on my laptop harddrive. My computer name is "Brian-HP". So, for the server name I specify "Brian-HP\SQLEXPRESS.
This doesn't work with the Management Studio. As previously mentioned, I get an error stating that it can't connect to the server.
However, if I use the Import / Export tool that loads with the MS SQL Express, then it appears to work, but when I try to open the database, I get an error message saying "Access is denied".

I'm having trouble finding clear instrutions on the internet. If anyone can provide some guidance I would greatly appreciate it.


Thanks,
btmcad

[EricPeterson87]

sqlite for .net has native 64 bit code... =)

[kareninstructor]

In regards to getting your MS-Access data into MS-SQL Server have your tried the wizard in MS-Access to create a new SQL Server database? Once the process is finished there will be links to the new SQL database. In server manager you can then open the database as normally would.

[Shaggy Hiker]

I have downloaded the MS SQL Server Express and the MS SQL Management Studio.

So far, I've found both of these to be very unintuitive, but that may just be my inexperience with database systems.

No, it's a common complaint. There were many odd choices made regarding the UI in SQL Server. You get used to it pretty quick, but they could have made a better front-end. I think it was just that SQL Server Management Studio was largely intended for people who are fairly proficient. In Access, if you double clicked a table, you'd get the contents of the table. In SQL Server, you have to right click on the table and select Edit Top 200 Records, to get similar functionality (and not the same functionality, as you only get 200 records and have to show and alter the SQL to get the same functionality as Access). I could come up with reasons for this decision, but not particularly sound ones.

[btmcad]

Finally, after playing with it for a couple hours this morning, I got it working.

I did use the Access wizard to convert the file to an MS SQL file. I had tried this a couple days ago, but couldn't get it to work. I realized the problem was that the original file was the older .mdb format. Today, I converted it to the newer .accdb format, then tried the SQL wizard and it worked fine. From there, I was able to view the file in Server Management Studio and make a few minor changes.

I modified my code in VB.net to include the proper SQL commands. Now everything works in Acad and properly updates the database.

Thanks to everyone for the help and suggestions.

Next I need to install this on the network. I'm sure I'll have questions on how to properly set this up on the network, but I'll start another thread for that.

thanks,
btmcad