Pass through Query without ODBC

[hegartyjp]

Hi All,

Posted on here a few days ago regarding connecting from Access to SQL without ODBC. Thanks to the replies I discovered ADODB and I now do not need to set up 200 ODBC connections. Or so I thought.

Unfortunately there is one further problem....

I have used ADODB to create the connections without using an ODBC connection.

This works fine when using code to create SQL Strings and run them.

However I also use a main pass through query for the reports and just dynamically chane the procedure it points to depending on whet report is requested.

This pass through Query has the following line in the properties


ODBC;DSN=CCR;Description=CCR;UID=a999157;DATABASE=CCR;Trusted_Connection=Yes


What I need to know is if there is any other way to get the query to connect without this line in the properties.

The field is actually called ODBC connect String so I am not sure there is any other way.

If anyone has any ideas can you please please reply to this.

Thank you so much

 

[kjv1611]

Unfortunately there is one further problem....

Is there an actual problem, or are you wanting to do it differently?

Some sort of error messages?

I don't see where you stated what the actual problem or error message was...

 

[hegartyjp]

Thanks for replying. Hope you can help.

The problem is with the pass through query.

It seems to need an ODBC connection in the properties.

So while everything I do in the code works with my ADODB connection. My pass through query no longer works.

I only use one pass through query and when you open a report it changes the sql in the query to suit the report.

Is there any way to get a pass through query to run without the ODBC string in the properties.

Or is there any way to get access to create the ODBC connection.

I am trying to get around setting up ODBC connections manually on every ones pc when they want to use the system.

There are 200 users to start and new ones coming all the time.

Thanks again

 

[hegartyjp]

Have you given up.

This is really beginning to drive me mad.

It is either something really simple or it can not be done.

Can any one help.

Either solution would be greatly appreciated.

1) Code to set up an ODBC connection to SQL Server via Access


2) Some way to get a pass through query stored in access to run without having an ODBC link set up.


Please Please Please.....

 

[kjv1611]

I can't think of a sollution off the top of my head. Have you tried searching around here or on Google to see if someone has dealt with this specific problem in the past?

I find that oftentimes I can find the answers I need by doing a Google search, and just refining it as I go if need be.

Just a thought.

 

[hegartyjp]

Thanks for trying fella.

I found a little code to set the connection string in the pass through query and it works.

Not sure how as the string indicates that there is an ODBC lingk even though there is not.

I will put it up here tomorrow in case any one else looks at this post.



Thanks again

 

[PHV]

Have a look here:

http://www.connectionstrings.com/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hegartyjp]

Just in case anyone was looking for this.

If you put this string in the ODBC String property for a pass through query in access, you do not need an ODBC connection


ODBC;Driver={SQL SERVER};Server=SERVERNAME;Database=DATABASENAME;Trusted_Connection=Yes


Thanks to all who tried to help