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

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!

 

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.

 

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).

 

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?

 

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.

 

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

 

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.

 

Thank you all very much! this is helping me a lot.

 

My pleasure