What is Access 2016 OLEDB connection string

I use Provider=Microsoft.ACE.OLEDB.12.0;Data Source=
for Access 2013 but this doesn't seem to work with Access 2016
ThomasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Microsoft.ACE.OLEDB.16.0; should be correct.

15 was Access 2013, 14 was Access 2010, and  12 is 2007.

Haven't tried it myself as yet though.

Jim.
0
ThomasAuthor Commented:
Something else must be involved because i have been successfully using 12 for Access 2013.
0
ThomasAuthor Commented:
Microsoft.ACE.OLEDB.16.0 doesn't work for me.

I purchased and installed Office 365, which includes Access and its About says 'Access 2016'.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Something else must be involved because i have been successfully using 12 for Access 2013.>>

Depends on what you have installed and registered as a provider.   If all that is on the station is 2016, then it should be the 16 provider.

 I have not at yet used Access 2016, so I'll need to do some checking.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Is Access 2016 installed 32 or 64 bit?   And are there any other versions of Access installed on the PC?   That includes the runtime or standalone engine componets.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Also, please look for:

C:\Program Files\Common Files\Microsoft Shared\OFFICE16\ACEOLDDB.DLL

and

C:\Program Files\Common Files (x86)\Microsoft Shared\OFFICE16\ACEOLDDB.DLL

right click on them, select properties and give me the version numbers.

 If they do not exist, then see if there are any other OFFICExx folders under \Mircosoft Shared  and look for the dll (actually, a search in Files Explorer probably would be faster).

 Need where you find them and what the versions are.

Jim.
0
ThomasAuthor Commented:
OS is 64 bit; Access 2016 is 32-bit.
No other versions of Access are installed.

Assuming you meant ACEOLEDB.DLL rather than ACEOLDDB.DLL.
Files Explorer found 1 instance of ACEOLEDB.DLL in
C:\Program Files (x86)\Microsoft Office\root\VFS\ProgramFilesCommonX86\Microsoft Shared\Office16
ACEOLEDB.DLL Version # = 16.0.7766.6032
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Assuming you meant ACEOLEDB.DLL rather than ACEOLDDB.DLL. >>

 Yeah, sorry, typo.

<<Files Explorer found 1 instance of ACEOLEDB.DLL in >>

 OK, so something then is wrong with the registration.

 Try running a repair on the office install and see if that takes care of it.  If not, then something is mucked up.  I don't remember how to re-register it manually, but I will find out if the repair doesn't do it for you.

Jim.
0
ThomasAuthor Commented:
Note that my Access 2016 is Office 365, which may have a different organization on DLL files.

I did an update but there is no 'repair' on Office 365. The problem remains.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I did an update but there is no 'repair' on Office 365. The problem remains.>>

Press the Windows Logo Key, type in "Control Panel", go to "Programs and Features", Select Microsoft Office 365, then Change.   You should have an Online Repair option.

 Choose that.

Let me know.

Jim.
0
ThomasAuthor Commented:
I did the online repair; however, the problem remains.

Since my issue involves both vb.NET and Access, I plan to connect with Scott McDaniel for help.

Thanks, Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
That's a good idea...Scott's done mostly .Net for the past few years, were I've been mostly Access.

But I didn't realize this involved .Net.

One thing for sure then is you want to make sure your project is compiled in same bitness as the Access install.  If not, you're going to get a not registered like you are.   If Access is 32 bit, then the .Net project must be compiled 32 bit.     And it must be done so specifically.  Leaving it on the default setting doesn't work.

Jim.
0
ThomasAuthor Commented:
Jim, thanks, I will verify that.
0
FERNANDO SALLESCommented:
por favor, alguém pode me fornecer uma string de conexão com VISUAL BASIC 6.0 COM ACCESS  2016
0
ThomasAuthor Commented:
Fernando, exactly what is your connection string in vb.net that connects to Access 2016? Thanks.
0
FERNANDO SALLESCommented:
Public Function SalvarCliente(ByVal NOME As String, ByVal CPF As String, ByVal ender As String, ByVal cid As String, _
ByVal bai As String, ByVal telef As String, ByVal cep As String)

Dim sql As String
Dim rs As adodb.Recordset

   
   
   
    sql = "insert into cliente(NOME,CPF,END,CEP,CIDADE,BAIRRO,TELEFONE) values ('" & NOME & "','" & CPF & "','" & ender & "','" & cep & "','" & cid & "','" & bai & "','" & telef & "')"
   
    Set rs = New adodb.Recordset
    rs.CursorLocation = adUseClient
    rs.Open sql, con, adOpenKeyset, adLockOptimistic  "the error is in this line "object not opened"
0
ThomasAuthor Commented:
Fernando, your code snippet does not define 'con', which is actually what I'm interested in. Thanks.
0
ThomasAuthor Commented:
It appears that Fernando has a solution; however, he did not define the variable 'con'. I added a comment asking him for that definition and he has yet to respond. Jim, maybe you could contact him to provide his definition of the 'con' variable.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Thos,

Are you linking to an Access database from an Access database? If so, in the connection string, what about skipping specifying the provider and simply do this:
;DATABASE=c:\path\filename.accdb
?

if you have a Click-to-Run installation, this page has some good information, including a link to get Microsoft Access Database Engine 2016 Redistributable so that the calling application can locate driver and provider:

Can't use the Access ODBC driver or OLEDB provider outside Office Click-to-Run applications
https://support.microsoft.com/en-us/help/2874601/can-t-use-the-access-odbc-driver-or-oledb-provider-outside-office-clic

~~~
if what you need to do is run an action query that, for example, appends records to a table in another Access database, you can circumvent linking to it and specify an IN clause for the SQL

INSERT INTO tablename (fields)
IN 'C:\folder\MyDatabase.accdb'
SELECT fields
FROM table ... INNER JOIN table2 ON ...
ORDER BY fields;

have an awesome day,
crystal
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ThomasAuthor Commented:
I am linking a vb.net program to access database. I followed crystal's links and installed Microsoft Access Database Engine 2016 (32-bit version in my case). With that in place, my connection from vb.net to Access was successful and was able to extract values from the Access database and use in the vb.net program.

I used a connection string of:
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & datafile & ";" & "Jet OLEDB:Database Password=" & password & ";"

This solution was way beyond my pay grade. Thank you crystal. You're a genius.
1
ThomasAuthor Commented:
Thanks again crystal.
1
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Thos ~ happy to help
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.