#  Other Applications & Softwares  > Access Tables & Databases >  > [SOLVED] Create connection between Excel and Access - queries

## jaryszek

Hi Guys,

I have connection string like below:




```
Please Login or Register  to view this content.
```


This is working when in SQL command there is :




```
Please Login or Register  to view this content.
```


Now 2 things: 
1. When i have password on my split database it is not working, where can I put the password in connection string?
Better is using DSN or Microsoft JET?
2*. I want to create connection between Excel worksheet and Access Query, not table, it is possible?* 


Bez*tytu?u.png

Please help Guys,
I didn't find the solution in internet,
Best wishes,
Jacek Antek

----------


## jaryszek

I am using MS Office 2010, split database (FE with accde extension and BE with accdb extension). 

It is relational database so pulling out only tables it is wrong approach so query would be the best. 
Table have foreign keys only and whole data is written in other tables...
these tables i should combine somehow in Excel or better in source - in Access database as query.

----------


## jaryszek

hi Guys,

anyone? 

Best wishes,
Jacek

----------


## jaryszek

Is it no possible ? 

Jacek

----------


## sandy666

Maybe you should ask moderator to move your thread here: Access Tables & Databases

Note: I said: maybe  :Smilie:

----------


## jaryszek

Moderator,

I am asking you to move my topic ;-) 

Jacek

----------


## AliGW

Moved as requested.

----------


## jaryszek

Anyone?

the last time here,
best wishes,
Jacek

----------


## jaryszek

Hi Guys,

it is possible to do that?

----------


## jaryszek

I have found that power query has possibility to do it. 
But without it?

----------


## alansidman

Here is what my data connection looks like for getting the results of a query into an excel worksheet.

----------


## jaryszek

Thank you my friend, 

i have noticed that if you have query only from one table - Excel is seeing this and i can create connetion. 

But if i am building query consisting of 2 tables and relationship between them - Excel will not see it. 

could anybody check if this is true ?

Thank you for help,
Jacek

----------


## xlnitwit

Hi,

I can connect to a query using more than one table. What does not show up for me is any query with an invalid SQL and of course any queries using VBA functions. I cannot recall if Nz is allowed either.

----------


## CK76

If it's two tables on same db. no issues. You can use single connection to query both in single statement.

If you are accessing two tables, each residing in separate db. You will need two connection, one for each db.
Then you'd query each separately into it's own recordset.

Once done, you can then loop through recordset(s) to manipulate or put it into result array as needed.
EDIT: For this you need to leverage ADO and use VBA. Can't do it with MS Query as far as I know.

It's much simpler to use PQ for this. As you can easily do joins, unions etc without needing to loop through recordset, once data is brought in from each db.

----------


## alansidman

> I cannot recall if Nz is allowed either.



  If NZ is part of the query, it will not come over to Excel.

----------


## jaryszek

Hmm very strange. tahnk you for your help.

Let's do test. 

I am connecting into Access frond-end which has linked table with Access Back-end with password. 

In Access Front-end i can not see the relationships between tables (maybe this is a reason?).

I am joinign Excel with Access Front -end. 

I made 2 queries: test1 and test2. 
test1.png

test2.png

And now i am creating new connection with Excel:
Data--> from Access database --> i am pointing to my front-end 
and the result is:

query.png

Why it is possible? 
I am seeing only query with one table. 

Best Wishes,
Jacek

----------


## CK76

Yep. NZ will throw error even when using ODBC DSN. And often won't be able to see query using standard method.





> [Microsoft][ODBC Microsoft Access Driver] Undefined function 'nz' in expression.

----------


## jaryszek

Ok, Nz I am understanding. 

I created sample database in Access 2007 format (accdb). 
Password for BE is: 1234

Can i pull out Qry1 and Qry2 from FE? 

Could you please help and check if you can do it and what is a connection string or steps how to do it ?

Thank you in advacnce,
Best wishes,
Jacek

----------


## CK76

PQ is definitely able to see it.
0.JPG

Also using ODBC DSN I was able to see all tables and queries.
1.JPG

----------


## jaryszek

Power query is working without any problems, but problem is that i want to give the user posibility to resfresh table without having power query. 

The strange thing is that i have the same connection string as you , the same sql command like you:

sql command.png

and if i manually point to Excel my database path there will be shouting: this source data do not contain any tables. 
And if i click ok :

odbc driver.png

odbc access driver - wrong password. 
To front-end i do not have any password... 

[edited out]

Maybe problem with Access ODBC driver? 

Best Wishes,
Jacek

----------


## alansidman

Agree with CK76. Using PQ and changing the path to the Back End, I was able to see both queries in Excel.

----------


## CK76

Couple of suggestions.

1. Avoid use of \Desktop\
In many companies, IT provisions Desktop to each network terminal using Logon Script etc. What this means is that your local Desktop may not match what's provisioned. Best to avoid use of Desktop for any code/connection related folders.

2. Update your driver, also ensure bitness of DB and driver match.

Other than that, I'm not sure where your issue stems from.

----------


## xlnitwit

It does appear to be an issue with the password on the BE database and the ODBC driver. If you remove that password, everything works fine. Perhaps using a pass-through query might help.

----------


## CK76

Odd. I had no issue with PW on my end (just had to update path of linked table).

I was able to pull data from Qry1 & 2.
0.JPG

----------


## xlnitwit

Like the OP I have 2010 and it does not work with either Data- From Access or using MS Query.

----------


## CK76

Use "From Other Sources" -> "From Data connection Wizard" and use ODBC DSN. 
"From Access"  will not be able to see it.

Though I'm using 2013 & 2016, MS Query hasn't changed over multiple versions. Hence, my guess at ODBC driver being the culprit.

----------


## xlnitwit

After removing the password from the BE, Data-From Access does work for me. With the password in place, no method works whether ODBC or directly OLEDB. I suspect it may relate to a change in the Jet provider. Interestingly, I could get the data into MSQuery itself, but as soon as I tried to return it to Excel, the password error occurred.

----------


## CK76

Wait, shouldn't it be ACE instead of JET engine to query "*.accdb"?

Try installing 2007 Office System Driver.
https://www.microsoft.com/en-us/down...190a24fa6=True

----------


## xlnitwit

ACE is just an updated version of Jet (it's still Jet, I think). I was using the ACE OLEDB provider since it comes with 2010.

----------


## CK76

Yeah, base technology is JET. And has 4.0 based architecture for backward compatibility.
Though JET won't connect to accdb.

Try different ACE (Access DB Engine), versions. From your description I suspect either ACE or ODBC being culprit.

----------


## xlnitwit

Our IT department won't allow me to do things like that!

----------


## CK76

lol. Figures.

Thank goodness I don't work for corporation.  :Wink:

----------


## jaryszek

Guys, 

i will check your solution (if i would find out what it is) on monday

Jacek

----------


## jaryszek

Hi!

I tested Microsoft Jet 4.0 OLE DB Provider and this driver does not see accdb files...
Also i was testing Microsoft Office 12.0 Access Database OLE DB Provider and still problem with password. 

What else i can use? 

Jacek

connections.png

----------


## jaryszek

Hi Guys,

i have found "solution":

https://answers.microsoft.com/en-us/...bac1d6a?auth=1

Could you please check if is this working for you ?

I do not know if I am implementing this ok but  it is no working for me. 

I will be grateful.
Best Wishes,
Jacek

----------


## CK76

Hmm I don't need to enter password while querying DB with Qry1 & 2.

Have you checked using different versions of Access Database Engine?

There are some known issue with "Microsoft Access Database Engine 2010 Redistributable". It's counter intuitive, but try using "2007 Office System Driver: Data Connectivity Components".
https://www.microsoft.com/en-us/down....aspx?id=23734

----------


## jaryszek

I do not know but still is asking about new password. 
If you hit the enter window popup will appear again and again....

echh, 

xlnitwit maybe you can try to use the method from my link before? 

Best wishes,
Jacek

----------


## xlnitwit

Yes- using legacy encryption works for me here.

----------


## jaryszek

Thank you ! 

I have polish access and i can not find this.

Could you please describe your steps one by one?

legacy description.png

File-->options-->client setting --> and this option which i have choosen in the picture above? 

And after that you put the password down, and set up new password?

Please give a tip ;-) 

Jacek

----------


## xlnitwit

Yes. I removed the password, selected the option  you have checked there and then reapplied the password. After that, it all worked.

----------


## jaryszek

Wow it is working !!!


thank you Guys for your help and support.

Jacek

----------

