1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

How can I Connect a VB interface to a SQL backend and/or an Access database?

Discussion in 'Software Development' started by Aladdin_SA, Apr 5, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. Aladdin_SA

    Aladdin_SA Thread Starter

    Joined:
    Apr 5, 2004
    Messages:
    4
    I working on my graduation project and I have to create a Visual Basic interface that will connect to a SQL database and/or an Access database. I know how to create the interface but I have no idea how to make it connect to a database! What kind of code do I need? Do I need middleware?

    I have looked at the Microsoft resource web sites but I couldn't find anything in this subject!
     
  2. AbvAvgUser

    AbvAvgUser

    Joined:
    Oct 3, 2002
    Messages:
    2,281
    TO OPEN THE ACCESS DATABASE:
    Set dbDatabase = DBEngine.OpenDatabase(App.Path & "\database.mdb", False, False, DBPwdString)

    TO OPEN A PARTICULAR TABLE/RECORDSET:
    Set rsTable = dbDatabase.OpenRecordset("NameTable", dbOpenTable)

    Both the variable dbDatabase (of type Database) and rsTable (of type Recordset) are global if you want to access it all throughout your application in various forms. Scope of these depend on the purpose. :)
     
  3. AbvAvgUser

    AbvAvgUser

    Joined:
    Oct 3, 2002
    Messages:
    2,281
    I would suggest take MSDN help on OpenDatabase and OpenRecordset to understand the option.

    Here's one line from it.
    Set database = workspace.OpenDatabase (dbname, options, read-only, connect)

    This is the way to connect using DAO (Data Access Objects). The more recent and recommended option is to use ADO (ActiveX Data Objects).
     
  4. Aladdin_SA

    Aladdin_SA Thread Starter

    Joined:
    Apr 5, 2004
    Messages:
    4
    Thank you very much!

    what do you mean take MSDN help? I'm not sure what is "MSDN on OpenDatabase" and where to get it? I'm really new to this whole thing!

    Also, does this code work with a SQL database?
     
  5. coderitr

    coderitr

    Joined:
    Oct 12, 2003
    Messages:
    3,015
    While the solution posted above will work with both access and SQL Server (through ODBC), I would suggest using ADO instead:

    Dim conn as New ADODB.Connection
    conn.Open "<your database connection string>"

    Now you have an open connection to the database...

    Dim rst as New ADODB.Recordset
    rst.Open "<your sql statement>"

    Now you have a recordset object containing the results of your query to do with what you want.

    The beauty of ADO is that the developer doesn't need to know (or care) what the underlying database type may be. You can use syntax in your SQL code that is specific to SQL Server or Oracle or mySql or whatever. An ODBC connection won't allow you to do that.

    AbvAvgUser's post refers to the Microsoft Developer Network library at http://msdn.microsoft.com -- Microsoft's freely accessible library of all Windows development tools.

    Also, http://www.pscode.com is a very helpful resource for new developers. The freely downloadable source code shows how to do most anything. Even those of us who have been software developers for years refer to this site for help with certain techniques or to find a better way to accomplish something from time to time.
     
  6. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    770
    Hello, im not sure if you have already done it but heres another way to connect vb to access, i use this all the time because its the best way i feel to do such a thing. firtsly make a modual and put this in

    ''''''''''''''''''''CODE''''''''''''''''''''''''

    Public DBConnection As ADODB.Connection
    Public TheRecord As ADODB.Recordset

    Public userName As String
    Public ODBCDSN As String

    Public Function OpenDataBase()
    On Error Resume Next

    'The database with the name snooker inside odbc is opened
    'No username or password provided
    'If you had set a password and username within odbc
    'for this database you must enter then here

    ODBCDSN = "snooker"
    userName = ""
    UserPassword = ""

    Set DBConnection = New ADODB.Connection
    DBConnection.Open ODBCDSN, userName, UserPassword
    If Err Then
    MsgBox "The database could not be loaded program will now end", vbOKOnly, "Error During Loading"
    End
    End If
    End Function

    '''''''''''''''''''''''END CODE''''''''''''''''''''

    all you have to do is go to control panel, and look for ODBC data source, add add the database if you are using win xp then go to control panel, performance and maintanence and then admin tools and ODBC. in the adbc form click add, select access and next and then add your database.

    you can use sql statements to add, search, delete, update and all other suff using this, but you can also do this with the other way that was suggested.

    in your code all you have to do is call the above function which will connect to the database, then all you have to do is write you sql commands
    eg:
    Dim sql
    sql = "select * from membership where memberid = '" & TheID & "'"
    Set TheRecord = DBConnection.Execute(sql)

    any more help let us know
     
  7. AbvAvgUser

    AbvAvgUser

    Joined:
    Oct 3, 2002
    Messages:
    2,281
    MSDN is Microsoft Developer's Network. It is the inbuilt help that comes with Visual Studio. You have to take help on OpenDatabase and OpenRecordset methods of the respective objects.
     
  8. Aladdin_SA

    Aladdin_SA Thread Starter

    Joined:
    Apr 5, 2004
    Messages:
    4
    Thank you all very much! this is helping me a lot.
     
  9. AbvAvgUser

    AbvAvgUser

    Joined:
    Oct 3, 2002
    Messages:
    2,281
    :D My pleasure
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/217504

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice