Solved

How do I connect to Access DB?

Posted on 2002-06-05
16
175 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

743 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

14 Experts available now in Live!

Get 1:1 Help Now