Results 1 to 16 of 16

Thread: ADODB for 64-bit

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    ADODB for 64-bit

    Hello:

    We just upgraded to SolidWorks and PDM 2021. I have some programs that utilizes accessing some spreadsheets to obtain data. Those programs use the Microsoft ActiveX Data Objects 6.1 Library. As I understand it, there are two versions of this file, one for 32-bit and one for 64-bit.

    32-bit: c:\Program Files(x86)\Common Files\ado\msado15.dll
    64 bit: c:\Program Files\Common Files\ado\msado15.dll

    The new Version of SolidWorks and PDM no longer support 32-bit applications, so my code is setup to only run in 64-bit mode.

    Below is my code:
    Code:
            Dim cn As String = "Provider = Microsoft.OLEDB.12.0; Data Source = " & "\\w2012\users\QUOTES\Templates\pricing report-2.xls" & "; Extended Properties = " & Chr(34) & "Excel 12.0; HDR = Yes; IMEX = 1" & Chr(34) & "; "
    
            Dim sql As String = "SELECT [Part ID], [Description], [Inv. UoM], [Last Unit Cost], [Avg. Unit Cost] FROM [Sheet1$]"
    
            Dim connExcel As New System.Data.OleDb.OleDbConnection(cn)
            Dim cmdExcel As New System.Data.OleDb.OleDbCommand()
            Dim oda As New System.Data.OleDb.OleDbDataAdapter()
            cmdExcel.Connection = connExcel
    
            connExcel.Open()
            Dim dtExcelSchema As Data.DataTable = connExcel.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, Nothing)
            connExcel.Close()
    I have manually registered both dll files, but am still getting the error:
    The 'Microsoft.OLEDB.12.0' provider is not registered on the local machine.

    The error occurs on this line of code:
    Code:
    connExcel.Open()
    I registered the dll as follows:
    Code:
    regsvr32  "C:\Program Files\Common Files\system\ado\msado15.dll"
    , and a box came up saying DllRegisterServer in C:\Program Files\Common Files\system\msado15.dll succeeded.

    I am not sure where to go from here. If I change my settings to support 32-bit, the SolidWorks and PDM portions of the program will not work. Basically, I cannot have this both ways.

    Thanks in advance!
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: ADODB for 64-bit

    According to a quick web search, 'Microsoft.OLEDB.12.0' is not a provider at all.

    Presumably you intended to use the JET provider (but you can't, as that is 32-bit only) or more likely the ACE provider.

    You can see valid examples of connection strings for ACE here:
    https://www.connectionstrings.com/ace-oledb-12-0/

    With ACE you are likely to need to install it, and you can find info about it here:
    https://www.connectionstrings.com/ac...-and-download/
    (note that you have a choice of 32-bit or 64-bit)

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: ADODB for 64-bit

    Hello:

    I think I had most of this covered. Even the installation of ACE.

    Slight change to the connection string for xls file as follows:
    Code:
    Dim cn As String = "Provider = Microsoft.OLEDB.12.0; Data Source = " & "\\w2012\users\QUOTES\Templates\pricing report-2.xls" & "; Extended Properties = " & Chr(34) & "Excel 8.0; HDR = Yes" & Chr(34) & "; "
    Same error. Suggests even though I have registered the dll files, they may not actually be registered. I've heard there can be multiple files and the correct one can be found in the system registry, but upon searching I do not see this.

    If I cannot get this working, is there a better way to read Excel data as though it were a database?

    Thanks for your reply!
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: ADODB for 64-bit

    This part is still wrong:
    Provider = Microsoft.OLEDB.12.0;
    ...and that will cause the error.


    Make sure you properly copy one of the examples in the link I provided.

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: ADODB for 64-bit

    Dim cn As String = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " & "\\w2012\users\QUOTES\Templates\pricing report-2.xls" & "; Extended Properties = " & Chr(34) & "Excel 8.0; HDR = Yes" & Chr(34) & "; "

    Same issue, it thinks ADODB is not registered.
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: ADODB for 64-bit

    Quote Originally Posted by ssabc View Post
    Same issue, it thinks ADODB is not registered.
    That is not the case, ADODB was not an issue at all.

    The previous issue was that the provider Microsoft.OLEDB.12.0 was not registered (and it wasn't, because it doesn't even exist).


    As you have now corrected that part of the connection string, you cannot be getting the same issue. You are presumably now getting an error about Microsoft.ACE.OLEDB.12.0 not being registered, and that is a different issue.

    You need to make sure that ACE is installed, and you can find info about how to install it in the second half of post #2 above. If you need 64-bit, make sure that the 64-bit version is installed.

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: ADODB for 64-bit

    I had also done that!

    The link provided downloaded 'AccessDatabaseEngine_X64.exe'.

    I just did a repair on the install, and the issue persists. Is there some other program? Was the link wrong?

    I also installed the 2016 version along with the 2010 version referenced in your post, located here: https://www.microsoft.com/en-us/down....aspx?id=54920
    Last edited by ssabc; Jan 18th, 2022 at 10:27 AM.
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: ADODB for 64-bit

    What is the actual error you are getting now?

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: ADODB for 64-bit

    Quote Originally Posted by si_the_geek View Post
    What is the actual error you are getting now?
    This is the same error I've been getting all along:
    'The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.'

    Name:  2022-01-18 10_32_40-SWExtractCutlist (Debugging) - Microsoft Visual Studio.jpg
Views: 1461
Size:  19.4 KB
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: ADODB for 64-bit

    are you registering with an elevated prompt?
    as a std user the registration may be virtualized
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: ADODB for 64-bit

    Hello:

    I am registering at an elevated (run as administrator) command prompt.
    I also did a repair on the ACE provider.

    I also looked through the local system registry and did see several references to msado15.dll. I also noticed, the 64-bit version may be showing up under 32-bit registry folders, see attached...

    Attachment 183671

    It's never been this complicated to have references in Visual Studio before....
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: ADODB for 64-bit

    It's never been this complicated to have references in Visual Studio before....
    i never figured out why something i could do in 10 minutes in VBA or VB6 took hours in .net and i could seldom get it to work right

    probably just too old
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: ADODB for 64-bit

    Hello:

    If there is any hope for an answer to this question, I would greatly appreciate it. Perhaps something else than ADODB to get information from Excel spreadsheets and be able to reference columns as fields.

    Thank you...
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: ADODB for 64-bit

    i note that on this computer both 32 bit and 64 bit versions are mentioned in the registry, even though i do not believe i ever deliberately installed them

    i do have visual studio of some version on my laptop so i may have a play over the weekend, but i will not promise any resolution of your issue and it will help little if the problem is still an installation issue on your computer
    i do not have a 64bit version of excel and am not sure if that will make any difference to reading a workbook with ADO /ACE, though it should not as excel does not need to be installed at all for this
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  15. #15

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: ADODB for 64-bit

    Pete:

    Mu dilemma is that my SolidWorks and PDM applications no longer work in 32-bit mode starting with the 2021 version. I have one app that uses ADODB and the SolidWorks API’s. I can no longer have the best of both worlds. Either one function works, or the other, never both. I am certainly open to other ways to do this, but greatly prefer to treat Excel tables like databases, where the columns are fields and the rows are records.

    Thanks!
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: ADODB for 64-bit

    after spending a ridiculous amount of time for a simple piece of code i found i had no problem to use your code to open a connection to an excel sheet and with a little editing put a selection of data into a data table, checked the column (fields) count
    i did nothing regarding installation of oledb, just used what was already there, office 2010 (32 bit) was installed previously, but the license is no longer valid after a windows 10 reinstall

    that all said it would appear that the problem is related to your installation of oledb, probably nothing we can help with from here
    try a fresh installation of everything
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width