Solved

connecting to sql server from access 2010 accdb

Posted on 2013-01-13
8
1,122 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
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…

911 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

22 Experts available now in Live!

Get 1:1 Help Now