Problem with Microsoft.Jet.OLEDB.4.0

[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

[kareninstructor]

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

[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