topic Re: VBA connection ms.access vs AutoCAD (subscript out of range) in VBA Forum

VBA connection ms.access vs AutoCAD (subscript out of range)

Anonymous — Fri, 29 Mar 2019 12:25:58 GMT

In the past i was allways use this vba file to make connection to ms.access database.

But on some reason it give me now connection error with "subscript out of range"

Private Sub Class_Initialize()
    On Error GoTo errInit
    Set adoConnection = New ADODB.Connection
    Set adoRC = New ADODB.Recordset
    With adoConnection
        .Open "Provider=Microsoft.JET.OLEDB.12.0;Data Source=" & "E:\TEST\Info64.accdb"
    End With
    On Error Resume Next
    ThisDrawing.SelectionSets("KADER").Delete
    Set objSSet = ThisDrawing.SelectionSets.Add("KADER")
    Exit Sub
errInit:
    MsgBox err.Description, vbCritical, "Ado Connection"
    Set adoConnection = Nothing
    Set adoRC = Nothing
    err.Raise err.Number, Me, err.Description

Re: VBA connection ms.access vs AutoCAD (subscript out of range)

Ed__Jobe — Fri, 29 Mar 2019 14:17:52 GMT

Have you set a breakpoint and stepped through the code to see where the error is coming from?

Re: VBA connection ms.access vs AutoCAD (subscript out of range)

Norman_Yuan — Fri, 29 Mar 2019 14:39:17 GMT

You need to tell which OS (Windows) version and which AutoCAD version that worked BEFORE and the versions of OS/AutoCAD where the code stops working.

Also, since you are connecting to *.accdb file (Access2007 or later), are you sure the DB drive in the connectionstring is:

Provider=Microsoft.JET.OLEDB.12.0

not

Provider=Microsoft.ACE.OLEDB.12.0

???

AFAIK, there is no "Microsoft.Jet.OLEDB12.0", rather the latest provider to Jet Engine is "Microsoft.Jet.OldDb.4.0", which no longer comes with later Windows OS (Win7 or later).

If you connect to *.accdb, the provider should be "Microsoft.ACE.OleDb.12.0". If you are using 64-bit AutoCAD, you need to either install 64-bit MS Access, or 64-bit MC Access DB Engine (free download), which is replacement to old Jet Engine.

Re: VBA connection ms.access vs AutoCAD (subscript out of range)

Anonymous — Fri, 29 Mar 2019 15:15:30 GMT

I using windows7 64bit, microsoft access 64bit and autocad201864bit.
before i was using access32bit and autocad2015

i have no idea what happend and why it not working anymore?

Re: VBA connection ms.access vs AutoCAD (subscript out of range)

Norman_Yuan — Fri, 29 Mar 2019 15:55:58 GMT

I assume that your computer (or any computer that runs the VBA code) had MS Access installed when it worked with AutoCAD 2015 (it must also have been 32-bit).

Now that you are using 64-bit AutoCAD, and its VBA now is also 64-bit, you MUST use MS Access DB Engine 64-bit. If you have 64-bit MS Office/MS Access installed, then you should be OK. However, in most cases with MS Office and/or MS Access installation, 32-bit MS apps are installed. Thus, you need to:

1. Change your MS Office suite/MS Access to 64-bit, which would get MS Access DB Engine installed.

2. Download and install 64 bit MC Access DB Engine (co-existing with 32-MS Office/Access app). This would be tricky, to say the least. You can search the net for how to install 64 MS Access DB Engine with MS Office suite 32bit co-existence.

IMO, 1 would be easier.

The other option is to move away from using Access DB for data storage to avoid the troublesome co-existence of 64-bit DB engine and 32-bit MS office suite issue.

Again, in the code's ConnectionString, the provider should be "Microsoft.ACE.OLEDB.12.0".

If you search this forum, you would find quite a few discussions on this topic in the past when people stated using 64-bit AutoCAD.

Re: VBA connection ms.access vs AutoCAD (subscript out of range)

Anonymous — Sat, 30 Mar 2019 12:18:32 GMT

Does anyone can give me an example how it works now with 64bit how i can connected to an ms.access database.

if i know what code i need to use to make this connection i can fix mine program but now it doesnt work.

Re: VBA connection ms.access vs AutoCAD (subscript out of range)

Norman_Yuan — Sun, 31 Mar 2019 12:52:58 GMT

I am not sure if you have read my previous reply or not.

Before anyone can give any suggestion on the code itself, you need to tell:

DO YOU HAVE MS ACCESS 64-bit INSTALLED; OR DO YOU HAVE MS ACCESS DB ENGINE 64-bit INSTALLED? If not, it is not code issue (although there is code error as I pointed out).