Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002

    Unanswered: How do I connect to a Database via code?

    Can someone give me generic code to connect to a database (SQL) via VB code? My forum is too cramped and I want to know how to do it through code anyways.

    ~~ Tc

  2. #2
    Join Date
    Jul 2001

    Your question is too general for me to give you a good answer.

    Most SQL-addressible databases required specific DLL files and connection strings. "Minor" changes to the string can make them inoperable. Therefore, giving you a "generic" connection string is useless. There are many sites on the internet that can provide you with connection strings for major databases, as well as listing, and supplying, the required files.

    Your VB version is important. If, for example, you were planning to use VB3 (old technology) to connect to an Access 2002 database, you might have trouble because of the m_dac version. You should use at least version 2.5, preferrably 2.6. If you are going to use the MSJet driver then use MSJet version 4.0. If you need to upgrade your m_dac file, Microsoft has them available for download.

  3. #3
    Join Date
    Apr 2002

    Take a look at this then, please:

    Well, Im using VB 6, and my m_dac version is 2.6. I was just thinking something along the lines of:

    dim rs as new ADORS.Recordset
    dim co as new ADORS.Command "SELECT * FROM MyTable WHERE [User] LIKE '%Test%'",
    "DNS=MyDatabase", adOpenKeySet, adLockOptimistic, adCmdTxt

    If rs.RecordCount = 0 Then

    MsgBox "Unable to find any users", vbCritical + vbOKOnly, "WARNING!"

    End If

    Would that work?

  4. #4
    Join Date
    Jul 2001
    TC -

    I use the following code snippets to connect VB to MSAccess tables locally and in networks. It uses ADO and the MSJet driver. I put it in the globals.bas file. You must add the ADO data objects reference to your project.

    Public m_JETDir As String 'drive and directory of mdb file
    Public m_JETmdb As String 'name and extension of mdb file
    Public Xconx As ADODB.Connection
    Public Xcmd As ADODB.Command
    Public Xrs As ADODB.Recordset

    Sub JETCONX() 'make connection to MSAccess Database

    Set Xconx = New ADODB.Connection
    Set Xcmd = New ADODB.Command
    Set Xrs = New ADODB.Recordset

    Set Xconx = CreateObject("ADODB.Connection")
    Xconx.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Persist Security Info=False;" & _
    "Data Source=" & m_JETDir & "\" & m_JETmdb
    Set Xrs = CreateObject("ADODB.Recordset")
    Xrs.CursorLocation = adUseServer

    End Sub

    Sub JETDROP() 'drop connection th MSAccess Database

    Set Xconx = Nothing
    Set Xcmd = Nothing
    Set Xrs = Nothing

    End Sub

    If you are not sure how to use ADO, I strongly recommend checking: <continue next line>
    default.asp?url=/library/en-us/ado270/htm/ <continue next line>

    I compact and standardize my code with one module to handle inserts, one for deletes and one for updates. It makes commits, rollbacks, and error logging very easy. I pass only an SQL statement, which also simplifies calling stored procedures. Because of the time loss, I don't drop the connection until the application either errors-out or closes normally. Only recordsets are opened and closed.

  5. #5
    Join Date
    Jul 2009
    Hello everyone, I'm new here, and sorry if I'm necroposting this thread, but I spent last 3 days to solve this problem and I need your help.

    In this thread, it's mentioned a possible VB3 connection to a recent version of Access. Now I have a project in VB3 (I know it's time to migrate...) connecting to Access 2.0. Some tables have 700K records and it became very slow... So I thinked to migrate to Access 2000 or 2003.

    How can I connect a recent version of Access in a VB3 project??
    I have MSJet 4.0 and MDAC 2.7 installed.

    Thanks in advice
    Last edited by MickAngel; 07-24-09 at 03:25.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts