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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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

Aladdin_SA

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

take MSDN help on OpenDatabase and OpenRecordset to understand the option
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?
 
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.
 
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
 
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top