by Zen (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: Visual Basic 3.0
Difficulty: Beginner
Date Added: Wed 3rd February 2021
Rating: 
(7 Votes)
This is Easy. Very Easy. 10 lines of code, 30 of comments. I'm sick to death of trying to find simple, concise code that tells me what I need to know. This is an example of how to access a SQL Database from VB, and how to use the data returned.
VBcodersOption Explicit
Private Sub cmdConnect_Click()
' We're going to assume we're using a SQL Server
' that is named 'Server'. We're also going to assume
' the target database's name is 'Database'. The default
' UID in SQL is 'sa', so let's just use that, and the
' default password is either 'sa' or blank (I'm going to
' use blank). Finally, let's assume the Table name
' is 'Table'.
Dim objConnection As Object
Dim objContents As Object
Dim strSQL As String
' Create the ADO connection. This is the handiest way to
' connect to a database in my uneducated opinion, so if you
' disagree, write your own code. ;-)
Set objConnection = CreateObject("ADODB.Connection")
' Next, open the connection to the database.
objConnection.Open "Driver={SQL Server};Server=Server;Database=Database;uid=sa;pwd=;"
' Now, for this next part to make sense, you'll need at least
' a little experience writing SQL queries. This is the simplest.
strSQL = "SELECT * FROM Table"
' Finally, Create a Recordset using the SQL string we wrote above.
' What's happening here is the connection object (objConnection)
' is executing the SQL query, then building a recordset called
' objContents with the results returned from our query.
Set objContents = objConnection.execute(strSQL)
' Lastly, I bet you're wondering how to get at that data. Well,
' If you're only interested in the first value returned, I
' recommend this, quick and easy.
varResult = objContents(0)
' If you're looking to gather a value for a particular field
' in the table, this is the way to go. Just replace
' with your field's name (you DO need the quotes).
varResult = objContents("")
' So if you wanted to return every value, you can simply use a
' while loop and BOF (Beginning Of File) and EOF (End Of File);
' SQL gets pissy if you try to go past the end of the file.
While objContents.BOF = False And objContents.EOF = False
varResult = objContents("")
ListBox1.AddItem varResult
objContents.MoveNext ' This moves on to the next ROW
Wend
End Sub
