connecting to sql server from access 2010 accdb

Hi,

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:}
drtopservAsked:
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")
cn.open 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.
0
 
peter57rCommented:
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...
http://support.microsoft.com/kb/892490
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Do you need help just connecting?  See if this helps. http://www.mssqltips.com/sqlservertip/2656/simple-step-by-step-process-to-import-ms-access-data-into-sql-server-using-ssis/.

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.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
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?
0
 
peter57rCommented:
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.
0
 
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.
0
 
peter57rCommented:
Yes I agree - I was thinking more of the source & database values- I forgot about the provider difference.
0
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.