Solved

connecting to sql server from access 2010 accdb

Posted on 2013-01-13
8
1,106 Views
Last Modified: 2013-06-26
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:}
0
Comment
Question by:drtopserv
8 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 38773613
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38774294
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
 

Author Comment

by:drtopserv
ID: 38774386
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 38999271
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
 
LVL 77

Expert Comment

by:peter57r
ID: 38999709
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
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 500 total points
ID: 38999743
@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
 
LVL 77

Expert Comment

by:peter57r
ID: 38999759
Yes I agree - I was thinking more of the source & database values- I forgot about the provider difference.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now