Excel 2010 connect to Access 2010

[escott]

I want to execute SQL from excel to access. Run update, insert and select queries from excel to access. I have done this before with 2003, but with 2010 I think I am running into issues with connection strings lining up correctly with the reference library. Although I understand VBA pretty good, I don't know much about library and connection strings. I am not sure what reference library to select to go with my connection strings. I keep getting errors on connect like file not found or missing driver. Its like it cannot see the access database.

2010 is newer so there is less code on the web to copy. This is a very simple app. I am just missing something simple little to make it work and I don't know what it is. Please help.

I am hoping for a short simple piece of code from sub to exit sub and to know the references to go with it. I can make the code pretty late

 

[escott]

I could really use some help. Anything would be great. I am dead stuck.

 

[Norie]

Could you post an example of the code you are having problems?

By the way I can't quite see how you can't find relevant code.

Perhaps you've narrowed your search to much by being too specific.

For example if you just search for 'connection strings' you'll get plenty of results.

To narrow that down you could just add Excel or Access, even ADO if that's what you are trying to use.

In fact if you are using ADO them including it in your search terms might be the best bet.

 

[escott]

I just nailed it


This made the difference. Not sure why but it worked.


Replaced string Provider=Microsoft.Jet.OLEDB.4.0;

with string Provider=Microsoft.ACE.OLEDB.12.0;

 

[danielalmeida50]

Hy escott,

I'm using exactly this code and work very well. But when I use password it accuses "Invalid Password" despite the password is correct.

Myconn = Diretorio & "\RH_Access.accdb"
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("data source") = Myconn
'.Properties("User ID") = "Admin"
.Properties("Jet OLEDB:Database Password") = "daniel"
.Open
End With