Solved

connecting to sql server from access 2010 accdb

Posted on 2013-01-13
8
1,170 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 53

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

738 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