Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve and update 1 record at a time
I'm not sure how complicated this is, but thought it be worth asking...
I created a macro in excel using ADO to retrieve records from an Access database (code below). My db table is called tblSales and has 2 fields: saleID, flag (flag datatype=True/False, with a default of False). Currently the macro is pulling all saleIDs and storing them into Sheet2. I want to modify the macro to only pull one row record at a time and at the same time update the flag field of the current record to True. The reason for the flag is so when there are multiple users, a SaleID will only be retrieved once. Since we are pulling only 1 record at a time, the SaleID will only be stored in cell A1, Sheet2 each time. Therefore, the excel sheet will only display the current record at a time. I hope this makes sense, my code current code is below. I am new to programming and have been stuck with this one for awhile now. Thank you to anyone who can provide me with assistance! Sub GetRecord() Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim WSOrig As Worksheet Dim sSql As String Set WSOrig = ActiveSheet sSql = "SELECT saleID FROM tblSales" sSql = sSql & " WHERE Flag=False" MyConn = "C:\SalesDB.mdb" Set cnn = New ADODB.Connection With cnn .Provider = "Microsoft.Jet.OLEDB.4.0" .Open MyConn End With Set rst = New ADODB.Recordset rst.CursorLocation = adUseServer rst.Open Source:=sSql, ActiveConnection:=cnn, _ CursorType:=AdForwardOnly, LockType:=adLockOptimistic, _ Options:=adCmdText Worksheets("sheet2").Range("A1").CopyFromRecordset rst rst.Close cnn.Close End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve and update 1 record at a time
I'm not very knowledgable in this area so for what it's worth here's example
code that bring in field name and database items, one at a time. Maybe this will give you some ideas. Sub Demo1() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim fld As ADODB.Field Dim RowCounter As Long, ColCounter As Long Sheet1.Cells.ClearContents ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ThisWorkbook.Path & "\adodb_database.xls" & ";Extended Properties=Excel 8.0;" rst.Open "ourdb", cnn, adOpenForwardOnly, adLockReadOnly 'Fields names For Each fld In rst.Fields ColCounter = ColCounter + 1 Sheet1.Cells(1, ColCounter).Value = fld.Name Next 'List values for all fields record by record RowCounter = 2 Do Until rst.EOF ColCounter = 1 For Each fld In rst.Fields Sheet1.Cells(RowCounter, ColCounter).Value = fld.Value ColCounter = ColCounter + 1 Next RowCounter = RowCounter + 1 rst.MoveNext Loop rst.Close cnn.Close Set rst = Nothing Set cnn = Nothing End Sub -- Jim Rech Excel MVP "Markantesp" wrote in message ... I'm not sure how complicated this is, but thought it be worth asking... I created a macro in excel using ADO to retrieve records from an Access database (code below). My db table is called tblSales and has 2 fields: saleID, flag (flag datatype=True/False, with a default of False). Currently the macro is pulling all saleIDs and storing them into Sheet2. I want to modify the macro to only pull one row record at a time and at the same time update the flag field of the current record to True. The reason for the flag is so when there are multiple users, a SaleID will only be retrieved once. Since we are pulling only 1 record at a time, the SaleID will only be stored in cell A1, Sheet2 each time. Therefore, the excel sheet will only display the current record at a time. I hope this makes sense, my code current code is below. I am new to programming and have been stuck with this one for awhile now. Thank you to anyone who can provide me with assistance! Sub GetRecord() Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim WSOrig As Worksheet Dim sSql As String Set WSOrig = ActiveSheet sSql = "SELECT saleID FROM tblSales" sSql = sSql & " WHERE Flag=False" MyConn = "C:\SalesDB.mdb" Set cnn = New ADODB.Connection With cnn .Provider = "Microsoft.Jet.OLEDB.4.0" .Open MyConn End With Set rst = New ADODB.Recordset rst.CursorLocation = adUseServer rst.Open Source:=sSql, ActiveConnection:=cnn, _ CursorType:=AdForwardOnly, LockType:=adLockOptimistic, _ Options:=adCmdText Worksheets("sheet2").Range("A1").CopyFromRecordset rst rst.Close cnn.Close End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve and update 1 record at a time
thankyou!
"Jim Rech" wrote: I'm not very knowledgable in this area so for what it's worth here's example code that bring in field name and database items, one at a time. Maybe this will give you some ideas. Sub Demo1() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim fld As ADODB.Field Dim RowCounter As Long, ColCounter As Long Sheet1.Cells.ClearContents ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ThisWorkbook.Path & "\adodb_database.xls" & ";Extended Properties=Excel 8.0;" rst.Open "ourdb", cnn, adOpenForwardOnly, adLockReadOnly 'Fields names For Each fld In rst.Fields ColCounter = ColCounter + 1 Sheet1.Cells(1, ColCounter).Value = fld.Name Next 'List values for all fields record by record RowCounter = 2 Do Until rst.EOF ColCounter = 1 For Each fld In rst.Fields Sheet1.Cells(RowCounter, ColCounter).Value = fld.Value ColCounter = ColCounter + 1 Next RowCounter = RowCounter + 1 rst.MoveNext Loop rst.Close cnn.Close Set rst = Nothing Set cnn = Nothing End Sub -- Jim Rech Excel MVP "Markantesp" wrote in message ... I'm not sure how complicated this is, but thought it be worth asking... I created a macro in excel using ADO to retrieve records from an Access database (code below). My db table is called tblSales and has 2 fields: saleID, flag (flag datatype=True/False, with a default of False). Currently the macro is pulling all saleIDs and storing them into Sheet2. I want to modify the macro to only pull one row record at a time and at the same time update the flag field of the current record to True. The reason for the flag is so when there are multiple users, a SaleID will only be retrieved once. Since we are pulling only 1 record at a time, the SaleID will only be stored in cell A1, Sheet2 each time. Therefore, the excel sheet will only display the current record at a time. I hope this makes sense, my code current code is below. I am new to programming and have been stuck with this one for awhile now. Thank you to anyone who can provide me with assistance! Sub GetRecord() Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim WSOrig As Worksheet Dim sSql As String Set WSOrig = ActiveSheet sSql = "SELECT saleID FROM tblSales" sSql = sSql & " WHERE Flag=False" MyConn = "C:\SalesDB.mdb" Set cnn = New ADODB.Connection With cnn .Provider = "Microsoft.Jet.OLEDB.4.0" .Open MyConn End With Set rst = New ADODB.Recordset rst.CursorLocation = adUseServer rst.Open Source:=sSql, ActiveConnection:=cnn, _ CursorType:=AdForwardOnly, LockType:=adLockOptimistic, _ Options:=adCmdText Worksheets("sheet2").Range("A1").CopyFromRecordset rst rst.Close cnn.Close End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
record update VBA form | Excel Discussion (Misc queries) | |||
want to record date for each time I update a file - Excel 2007 | Excel Discussion (Misc queries) | |||
want to record date for each time I update a file | Excel Discussion (Misc queries) | |||
retrieve and update data from a dbf file | Excel Programming | |||
Code to Update a Record | Excel Programming |