Solved

Create ODBC via VBA for access

Posted on 2010-08-24
6
770 Views
Last Modified: 2013-11-27
I have an access front end that references a SQL backend.  I have the
ODBC connection connecting the front end to the back end set up on my pc and it works fine.  

My problem occurs when I distribute the front end to another user with out the ODBC connection
set up on their PC.  They recieve an error.  I have to distribute to a number of users but I do
not want them to set up the ODBC connection since that task is error prone and the users and not
all savy with computers.

how can I use VBA to set up the connection? Please use examples in your code.
0
Comment
Question by:RDLFC
[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
6 Comments
 
LVL 4

Accepted Solution

by:
Clothahump earned 167 total points
ID: 33516898
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

cnn.Open "YourDSN", "YourUsername", "YourPassword"
rs.ActiveConnection = cnn
rs.CursorLocation = adUseServer

rs.Source = "SELECT * FROM TABLE WHERE FIELD='data'"
rs.Open
' code goes here to manipulate the recordset retrieved
rs.Close

Set rs = Nothing
Set cnn = Nothing


If you don't have a DSN established, look at www.ConnectionStrings.com and find a connection string that fits your needs to use in the cnn.open line.
0
 

Author Comment

by:RDLFC
ID: 33517796
thanks i will check out your solution when I get to work tomorrow.
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 167 total points
ID: 33518807
Here is a full module with all functions for performing this.
Copy and paste into a new module and omit the first line:
Attribute VB_Name = "basWinAPI_DSN"

Study the in-line comments for typical usage and examples.
Note that the Private functions are for internal use - by the Public functions.

Be aware, that limited user rights may prohibit creation of DSN entries.

/gustav
basWinAPI-DSN.txt
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 15

Assisted Solution

by:fsze88
fsze88 earned 166 total points
ID: 33518851
I suggest

the result is

rst.CursorLocation = adUseClient
rst.CursorType = adOpenStatic
rst.LockType = adLockBatchOptimistic
should be better and able to reconnect to database several time
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset


Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset


cnn.Open "dsn", "user", "pwd"
rst.ActiveConnection = cnn
rst.CursorLocation = adUseClient
rst.CursorType = adOpenStatic
rst.LockType = adLockBatchOptimistic

rst.Source = "SELECT * FROM yourTable  "
rst.Open



rst.Close
cnn.Close

Set rst = Nothing
Set cnn = Nothing

Open in new window

0
 

Author Comment

by:RDLFC
ID: 33552606
thanks guys for your input, but I used the article at the site below and found it to be very helpful.

http://support.microsoft.com/kb/892490
0
 

Author Closing Comment

by:RDLFC
ID: 33552611
The comments assisted me in gathering an understanding but the article assisted in implementing the DNS less connection.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 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