How do I connect to Access DB?

How do I create a connection to an Access database?
And then how do I add that file to my executable?

Jeff
LVL 3
jeffbeasleyAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Here is the connection strong for Access:
http://www.able-consulting.com/ADO_Conn.htm#ODBCDriverForAccess

Anthony
0
 
rspahitzConnect With a Mentor Commented:
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
 
Anthony PerkinsCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
rspahitzCommented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
 
rspahitzCommented:
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
 
Anthony PerkinsCommented:
You got it!

Anthony
0
 
jeffbeasleyAuthor Commented:
I am getting reay to try this out I will let you all know
0
 
jeffbeasleyAuthor Commented:
reay --> ready
0
 
jeffbeasleyAuthor Commented:
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
 
jeffbeasleyAuthor Commented:
4. Make a reference to Microsoft ActiveX Data Objects
5. Make a reference to Microsoft OLEDB Service Component
????????????????????????????
0
 
rspahitzCommented:
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
 
DanRollinsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.