Solved

How do I connect to Access DB?

Posted on 2002-06-05
16
178 Views
Last Modified: 2010-05-02
How do I create a connection to an Access database?
And then how do I add that file to my executable?

Jeff
0
Comment
Question by:jeffbeasley
  • 6
  • 4
  • 4
  • +1
16 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 50 total points
ID: 7057384
Here is the connection strong for Access:
http://www.able-consulting.com/ADO_Conn.htm#ODBCDriverForAccess

Anthony
0
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 50 total points
ID: 7057750
Actually, I've found that the easiest way is to use the Data control wizard to get the string, then delete the data control.

To do this with ADO, add the Microsoft ADO Data Control 6.0 (under menu Project|Components).  Add this control to your form, go to the (custom) property (or right-click on it and go to properties) then follow the wizard:

* Select "Use Connnection String"
* Click the [Build] button
* Select the Access provider (Microsoft Jet 4.0 OLE DB Provider for Access 2000/ADO)
* Click [Next]
* Click on [...] and find the Access DB and [Open] it
* As necessary, specify a user name and password
* Click [Test Connection] to ensure that you got everything right ("Test Connection Succeeded")
* Click [OK]
* Copy the contents of the "Use Connection String" textbox and paste it into you code (maybe initially in a comment.)
* Delete the data control

Then use something like the following code:

  Dim conADO As ADODB.Connection
  Dim rstADO As ADODB.Recordset
  Dim strConnection As String
  Dim strDBFilename as String
  Dim strSQL As String
 
  Set conADO = New ADODB.Connection
  Set rstADO = New ADODB.Recordset
 
  strDBFilename = "C:\xxx\A2K.mdb"
 
  strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
  strConnection = strConnection & "Data Source=" & strDBFilename & ";"
  strConnection = strConnection & "Persist Security Info=False"
 
  conADO.ConnectionString = strConnection
  conADO.Open
 
  strSQL = "Select * from tblXXX"
  rstADO.Open strSQL, conADO.ConnectionString
 
  While Not rstADO.EOF
    Debug.Print rstADO.Fields(0).Name; rstADO.Fields(0).Value
    rstADO.MoveNext
  Wend
 
  rstADO.Close
 
  conADO.Close
 
  Set conADO = Nothing
  Set rstADO = Nothing
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7058329
rspahitz,

That seems like overkill.  It would seem that a simple project like the following would do the trick a lot better:

1. Start a new Project.
2. Add a Multiline Text Box (txtConnectionString)
3. Add 3 Command buttons (cmdNew, cmdEdit and cmdCopy)
4. Make a reference to Microsoft ActiveX Data Objects
5. Make a reference to Microsoft OLEDB Service Component
6. Add the following code
Private Sub cmdNew_Click()
On Error GoTo ErrHandler
Dim objDataLink As DataLinks
Dim strConn As String

Set objDataLink = New DataLinks
strConn = objDataLink.PromptNew
txtConnectionString.Text = strConn

Exit Sub
ErrHandler:
txtConnectionString.Text = vbNullString

End Sub

Private Sub cmdEdit_Click()
Dim objDataLink As DataLinks
Dim cn As ADODB.Connection
Dim strConn As String

On Error GoTo ErrHandler
Set objDataLink = New DataLinks
Set cn = New ADODB.Connection
cn.ConnectionString = txtConnectionString.Text
strConn = objDataLink.PromptEdit(cn)
txtConnectionString.Text = cn.ConnectionString

Exit Sub
ErrHandler:

MsgBox "An invalid ADO ConnectionString was detected. Please try again!"
txtConnectionString.Text = vbNullString

End Sub

Private Sub cmdCopy_Click()

With Clipboard
   .Clear
   .SetText txtConnectionString.Text
End With

End Sub

Private Sub txtConnectionString_Change()
Dim Enable As Boolean

Enable = Len(Trim$(txtConnectionString.Text))
cmdEdit.Enabled = Enable
cmdCopy.Enabled = Enable

End Sub

Anthony
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 22

Expert Comment

by:rspahitz
ID: 7059767
Anthony, other than the wizard part, our codes are similar, except that you add the "required" error trapping which I should have included.

The nice thing about my code is that you could make it into a subroutine and pass the DB name in with the SQL string, then return a recordset (depending on how big the recordset gets.)

In yours, I'm not sure why you're putting things in text boxes.  Maybe you interpreted the original question differently.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7059945
Perhaps.  The difference is that you require adding the ADO Data Control (which as an aside and as you probably know, I am particularly allergic to), doing what you have to do, and then remembering to remove it from the project. I am suggesting you write your own app that is separate from any current development you are doing.  This is how we do it here.  In other words, when we want a connection string we run the app which copies it into the clipboad and you can then very simply paste it into your code.

Just two different appraoches to do the same thing.

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7059956
The Text box is optional, you could just copy it to the clipboard, if you so wish.  I just like to see it, before I paste it in and it also allows you to edit it and return back to the wizard with the editted connection string.

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7059961
And if you think about it, the Data Control has its own "Text Box", it just happens to be in a Property Page.

Anthony
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 7060040
I see...I think I like your idea...essentially take the wizard concept and turn it into an app that puts the result into the clipboard for pasting.. :)

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7060048
You got it!

Anthony
0
 
LVL 3

Author Comment

by:jeffbeasley
ID: 7063122
I am getting reay to try this out I will let you all know
0
 
LVL 3

Author Comment

by:jeffbeasley
ID: 7063125
reay --> ready
0
 
LVL 3

Author Comment

by:jeffbeasley
ID: 7063188
I am actually trying to do this to populate a combo box with a list of data from my database.............

I have never used access in this capacity but I am familar with other languages............

---------------
Business Rules
---------------

I want to create an application that integrates  completely with my database (access)

And I want my application to be a data manipulator to populate tables and update tupples....

I want the exe to run and install both the database and VB forms...............

Any help appreciated.........

I am now trying to make my connection.........

Jeff
0
 
LVL 3

Author Comment

by:jeffbeasley
ID: 7063212
4. Make a reference to Microsoft ActiveX Data Objects
5. Make a reference to Microsoft OLEDB Service Component
????????????????????????????
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 7063223
To add the ADO reference, if you add the data access control, you get that automatically, but to do it manually:

1) Go to menu Project | References...
2) Scroll down the list and look for Microsoft ActiveX Data Objects 2.x Library (take the newest number, like 2.5)
3) Put a checkmark next to the desired choice
4) Click OK.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7858120
Hi jeffbeasley,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Split points between: acperkins@devx and rspahitz

jeffbeasley, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
using Access 8 68
Saving history changes to sub form 4 42
VB6 ListBox Question 4 42
Problem to With line 4 57
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

776 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