Jacob Hilderbrand
02-22-2007, 05:55 PM
Not really a VBA question, but I figure someone here can assist me. I am creating a database at work for tracking a lot of items. Normally what I would do in this case would be to use Excel and the user interface and Access as the backend DB.
But I want to try to make this an executable file. While I have made these before with Visual Studio, I haven't used a DB to store data and that is where I am having trouble.
Basically I need an example of how to retrieve data from a database and how to transfer Append/Insert it back.
Assuming the following:
Database: DB1.mdb
Tables: Table1
Fields: Field1, Field2, Field3
Can someone get me started on how to retrieve all the data to a collection or recordset or... (suggestions welcome).
Then how to save it back once it has been updated.
Thanks
Bob Phillips
02-23-2007, 03:38 AM
Jake,
Here is some code to maintain an Access database using ADO. Should be easily adapted
Sub AddData()
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"
sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
" VALUES ('Bob','Phillips','01202 345678','me')"
oConn.Execute sSQL
oConn.Close
Set oConn = Nothing
End Sub
Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"
sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText
' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
Else
MsgBox "No records returned.", vbCritical
End If
oRS.Close
Set oRS = Nothing
End Sub
Sub UpdateData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"
sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText
' Check to make sure we received data.
If oRS.EOF Then
MsgBox "No records returned.", vbCritical
Else
sSQL = "UPDATE Contacts " & _
" SET Phone = 'None' " & _
"WHERE FirstName = 'Bob' AND LastNAme = 'Phillips'"
oRS.ActiveConnection.Execute sSQL
sSQL = "SELECT * From Contacts"
oRS.ActiveConnection.Execute sSQL
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
End If
oRS.Close
Set oRS = Nothing
End Sub
Jacob Hilderbrand
02-23-2007, 12:25 PM
I got all of those to work.
Thanks :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.