connecting to sql server from access 2010 accdb


I have 2 access files , first is .adp and the second is .accdb

in the adp file i just connect  to sql server and run a query in vba code .
from .adp file i use File->server ->connection then insert the ip server,special port + username and password to connect to the sql server.

my needs now is to drop out this file and use instead the accdb file with a form that can run the same query i use in the adp , means i need a help with vba code that can be used in the accdb (in the form) that can connect to the sql server mentioned in the adp file.

thxn in regard:}
Who is Participating?
Vadim RappConnect With a Mentor Commented:
The easiest for you is to run the same query in the code like this:

set cn = createobject("adodb.connection") connectionstring
cn.execute your-query

Connectionstring - look at the connection parameters in ADP and construct connection string like in ConnectionStrings website, look for "Connect via an IP address", which also has the port - replace 1433 by yours.

your-query - take it directly from the ADP.

This is assuming that your query does not return results, such as INSERT, UPDATE, etc.
I would expect an adp to be replaced by an accdb using linked tables - in which case you can either create a dsn to use in linking the tables- which involves no code-  or else use a coded dsn-less connection as described in this article...
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Do you need help just connecting?  See if this helps.

If this part you already understand, maybe rephrase your question because the 2nd paragraph may make sense to you but I think it is not clear what you actually need.  I would give a sample form (shortened if long), expected outcome and what have you tried so far.  I have not used access in a long time so if somebody else does not chime in, I would repost the question and rephrase.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

drtopservAuthor Commented:
where should i put the port for the sql server.
it hard to me to understand how to do it?
is it possible to have sample form with a code?
There is no 'ready-made' solution.  The detail of the connection is specific to your network and server setup.

I would have thought that the adp connection string would probably tell you all you need to know.
Just do...?Currentproject.Connection in the immediate window in the adp file.
Vadim RappConnect With a Mentor Commented:
@peter57r, Access ADP is using Access-specific OLEDB provider, which is close to the standard SQLOLEDB, but not quite the same. ?Currentproject.Connection will show that provider, but it's probably better to replace it by SQLOLEDB.
Yes I agree - I was thinking more of the source & database values- I forgot about the provider difference.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.