Tech Support Guy banner
Status
Not open for further replies.
1 - 9 of 9 Posts

·
Registered
Joined
·
4 Posts
Discussion Starter · #1 ·
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!
 

·
Registered
Joined
·
2,281 Posts
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. :)
 

·
Registered
Joined
·
2,281 Posts
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).
 

·
Registered
Joined
·
4 Posts
Discussion Starter · #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?
 

·
Registered
Joined
·
3,015 Posts
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.
 

·
Registered
Joined
·
772 Posts
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
 
1 - 9 of 9 Posts
Status
Not open for further replies.
Top