473,287 Members | 1,492 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

How to get an increment of records in an Access table?

I am using two recordsets for inputting data into an access table. I have used two loops for each recordset to do so. My intent is to read data into the table from an Excelsheet. My first recordset selects all columns from the excelsheet and does what it is supposed to do. The second recordset selects only a handful of columns from the Excelsheet, but when using the second recordset I am only able to get the first row of data transferred into the table and not able to get all rows of data in. The second recordset starts from column number 30 and field number 27 in the table. The format of the first Recordset is very similar the only difference being that the 1st one uses cStartColumn1 as input in the function argument inside the for loop and the second one uses iCol which indicates the location of current column to which the cursor is pointing to. I will include the code for the second recordset here:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim rs2 As DAO.Recordset
  3.     Dim strSQL As String
  4.     Dim iColumn As Integer
  5.     Dim iField As Integer
  6.     Dim lrecords1 As Long
  7.     iCol = iCol + 2    'increments the column from 28 which is where the first recordset was stopped to 30 which is the starting column for the second.
  8.  
  9. strSQL = "SELECT [Field Measurement (m)],[Line Completed?], [Major Defects(codes only)],[CCTV Inspection Comments], [Date] FROM " & sTable
  10.     Set rs2 = dbs.OpenRecordset(strSQL)
  11.  
  12.  
  13.      With rs2
  14.     .AddNew
  15.      End With
  16.  
  17.  iRow = cStartRow1
  18.  iColumn = iCol
  19.  
  20.  Do While Not wks.Cells(iRow, 30) = ""
  21.  
  22.     cStartField1 = rs2.Fields(0)
  23.  
  24.     iField = cStartField1
  25.     lrecords1 = lrecords1 + 1    'this is the line where I am supposed to see the records incremented in the access table.
  26.  
  27.  For iColumn = iCol To iCol + (rs2.Fields.Count - (cStartField1 + 1))
  28.  
  29.          rs2.Fields(iField) = wks.Cells(iRow, iColumn)
  30.          DoCmd.Hourglass True
  31.          iField = iField + 1
  32.  
  33. If iField = 5 Then
  34.          rs2("Date").value = wks.Cells(3, 3)
  35.                  End If
  36.  
  37.       Next
  38.  
  39. iRow = iRow + 1
  40.       rs2.Update
  41.       rs2.AddNew
  42.  
  43.     loop
  44.  
  45.       rs2.Close
  46.       Set rs2 = Nothing
  47.  
  48.  
Feb 1 '11 #1
18 2779
ADezii
8,834 Expert 8TB
The following would be the Logic for Appending Data from one Recordset to another with Fields being misaligned:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst_1 As DAO.Recordset
  3. Dim rst_2 As DAO.Recordset
  4.  
  5. Set MyDB = CurrentDb
  6. Set rst_1 = MyDB.OpenRecordset("tblTest", dbOpenForwardOnly)
  7. Set rst_2 = MyDB.OpenRecordset("tblAppend", dbOpenDynaset)
  8.  
  9. With rst_1
  10.   Do While Not .EOF
  11.     rst_2.AddNew
  12.       'Don't forget that the Fields Collection is Indexed starting at 0
  13.       rst_2.Fields(0) = .Fields(1)
  14.       rst_2.Fields(1) = .Fields(2)
  15.       rst_2.Fields(2) = .Fields(6)
  16.       rst_2.Fields(3) = .Fields(8)
  17.       rst_2.Fields(4) = .Fields(10)
  18.     rst_2.Update
  19.        .MoveNext
  20.   Loop
  21. End With
  22.  
  23. rst_1.Close
  24. rst_2.Close
  25. Set rst_1 = Nothing
  26. Set rst_2 = Nothing
Feb 1 '11 #2
Thanks ADzeii, but I am looking for is being able to merge two different recordsets from the same table into the same table. So, one recordset has all the fields in table A and the other selects only a few fields from table A. I am doing this because I am skipping two columns in excel so by opening rst_2 I am only selecting the columns that come after the skipped columns. The other problem is only seeing one row of data displayed in my table for rst_2
Feb 2 '11 #3
ADezii
8,834 Expert 8TB
How about Posting some sample Data along with the desired results? In this manner, we can get a much clearer picture of exactly what you are requesting.
Feb 2 '11 #4
Hi, I have attached the original spreadsheet as For Me.xls
and I also copied what I have in my Access table into book1.xls and attached them both as a zip file.
So in there you'll see that my 2nd recordset fills up columns (Field Measurement through Date), but it's only one row of data.
Attached Files
File Type: zip Book1.zip (2.6 KB, 89 views)
File Type: zip For Me.zip (8.1 KB, 100 views)
Feb 2 '11 #5
ADezii
8,834 Expert 8TB
Without the Code in its entirety, and not having the Database to look at, I simply posted what I feel the general Logic may be:
Expand|Select|Wrap|Line Numbers
  1. Dim rs2 As DAO.Recordset
  2. Dim strSQL As String
  3. Dim iColumn As Integer
  4. Dim iField As Integer
  5. Dim lrecords1 As Long
  6. iCol = iCol + 2    'increments the column from 28 which is where the first recordset was stopped to
  7.                    '30 which is the starting column for the second.
  8.  
  9. strSQL = "SELECT [Field Measurement (m)],[Line Completed?], [Major Defects(codes only)], " & _
  10.          "[CCTV Inspection Comments], [Date] FROM " & sTable
  11.  
  12. Set rs2 = dbs.OpenRecordset(strSQL)
  13.  
  14. iRow = cStartRow1
  15. iColumn = iCol
  16.  
  17. DoCmd.Hourglass True
  18.  
  19. Do While Not wks.Cells(iRow, 30) = ""
  20.   cStartField1 = rs2.Fields(0)
  21.  
  22.   iField = cStartField1
  23.   'Do you really need this?
  24.   lrecords1 = lrecords1 + 1    'this is the line where I am supposed to see the
  25.                                'records incremented in the access table.
  26.  
  27.     For iColumn = iCol To iCol + (rs2.Fields.Count - (cStartField1 + 1))
  28.       rs2.AddNew
  29.         rs2.Fields(iField) = wks.Cells(iRow, iColumn)
  30.           iField = iField + 1
  31.             If iField = 5 Then
  32.               rs2("Date").Value = wks.Cells(3, 3)
  33.             End If
  34.       rs2.Update
  35.     Next
  36.       iRow = iRow + 1
  37. Loop
  38.  
  39. DoCmd.Hourglass False
  40.  
  41. rs2.Close
  42. Set rs2 = Nothing
Feb 2 '11 #6
I tried that format but it didn't work. If you look in For Me.xls you can see that from the last 4 columns in there only the first Row of data(23.0, NO, qwe, asdasd) is found in the Access Table(book1.xls) so for some reason the records dont get incremented in the table and the other 3 rows are lost. Also, how about merging the two recordsets rst and rs2? Will I be able to do that?
I will put in the rest of the code here just in case.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command20_Click()
  2.  
  3. msg = ProcessFileImport(sOutput, "CCTVPipeTemporary", 8, 2, 1, 1)
  4.  
  5. End Sub
  6.  
  7. Public Function ProcessFileImport(sFile As String, sTable As String, cStartRow1 As Byte, cStartColumn1 As Byte, cStartField1 As Byte, cTab1 As Integer) As String
  8.  
  9.  Dim wbk As New Excel.Workbook
  10.  
  11.    Dim ExcelApp As New Excel.Application
  12.    Dim ExcelBook As New Excel.Workbook
  13.    Dim wks As New Excel.Worksheet
  14.    Dim rngDefine As Excel.Range
  15.    Dim Rng As Excel.Range
  16.  
  17.    ' Access object variables
  18.    Dim dbs As DAO.Database
  19.    Dim rst As DAO.Recordset
  20.    Dim sSQL As String
  21.  
  22.  
  23.    Dim lrecords As Long
  24.    Dim iRow As Integer
  25.    Dim iCol As Integer
  26.    Dim iFld As Integer
  27.    Dim Message As String
  28.   ' Dim iShot As Integer
  29.    'Dim templ As String
  30.  
  31.   Set ExcelBook = ExcelApp.Workbooks.Open(sFile)
  32.   Set wbk = ExcelApp.Workbooks.Open(sFile)
  33.   Set wks = ExcelApp.Worksheets(cTab1)
  34.  
  35.  
  36. ' First Recordset
  37.  Set dbs = CurrentDb
  38.    sSQL = "SELECT * FROM " & sTable
  39.    Set rst = dbs.OpenRecordset(sSQL)
  40.  
  41.    With rst
  42.    .AddNew
  43.    End With
  44.  
  45.    iCol = cStartColumn1
  46.    iRow = cStartRow1
  47.  
  48. 'Stop
  49.    Do While Not wks.Cells(iRow, 1) = ""
  50.  
  51.    cStartField1 = rst.Fields(0)
  52.  
  53.       iFld = cStartField1
  54.       lrecords = lrecords + 1
  55.  
  56.       For iCol = cStartColumn1 To cStartColumn1 + (rst.Fields.Count - (cStartField1 + 1))
  57.  
  58.            If iCol = 28 Then
  59.               Exit For
  60.  
  61.             End If
  62.  
  63.          rst.Fields(iFld) = wks.Cells(iRow, iCol)
  64.          DoCmd.Hourglass True
  65.          iFld = iFld + 1
  66.  
  67.       Next
  68.  
  69.       'iCol = cStartColumn1
  70.       iRow = iRow + 1
  71.       rst.Update
  72.       rst.AddNew
  73.  
  74.  
  75.    Loop
  76.  
  77.   ' rst.Close
  78.   ' Set rst = Nothing
  79.  
  80.     Dim rs2 As DAO.Recordset
  81.     Dim strSQL As String
  82.     Dim iColumn As Integer
  83.     Dim iField As Integer
  84.     Dim lrecords1 As Long
  85.     iCol = iCol + 2
  86.     iFld = iFld + 3
  87.     iField = iFld
  88.  
  89.  
  90.  '2nd Recordset
  91.     strSQL = "SELECT [Field Measurement (m)],[Line Completed?], [Major Defects(codes only)],[CCTV Inspection Comments], [Date] FROM " & sTable
  92.     Set rs2 = dbs.OpenRecordset(strSQL)
  93.  
  94.      With rs2
  95.     .AddNew
  96.     End With
  97.  
  98.     'iColumn = cStartColumn1
  99.  
  100.  
  101.     iRow = cStartRow1
  102.    ' iField = cStartField1
  103.     iColumn = iCol
  104.   '  lrecords1 = iRow
  105.  
  106.  
  107.     Do While Not wks.Cells(iRow, 30) = ""
  108.  
  109.     cStartField1 = rs2.Fields(0)
  110.  
  111.     iField = cStartField1
  112.     lrecords1 = lrecords1 + 1
  113.  
  114.     For iColumn = iCol To iCol + (rs2.Fields.Count - (cStartField1 + 1))
  115.  
  116.          rs2.Fields(iField) = wks.Cells(iRow, iColumn)
  117.  
  118.              DoCmd.Hourglass True
  119.              iField = iField + 1
  120.  
  121.            '  lrecords = lrecords + 1
  122.  
  123.            If iField = 5 Then
  124.               rs2("Date").value = wks.Cells(3, 3)
  125.                lrecords = lrecords + 1
  126.            End If
  127.  
  128.            DoCmd.Hourglass False
  129.  
  130.       Next
  131.  
  132.       iRow = iRow + 1
  133.       rs2.Update
  134.       rs2.AddNew
  135.  
  136.    Loop
  137.  
  138.       rst.Close
  139.       Set rst = Nothing
  140.       rs2.Close
  141.       Set rs2 = Nothing
  142.  
  143.  
  144.  
  145.     Ltotal = DCount("*", "CCTV_Pipe_Daily_Report")
  146.     MsgBox ("Import Process is Done!" & vbclrf & vbCrLf & "There are" & Ltotal & " Records imported into the database")
  147.   ' ProcessFileImport = "Number of Pipes imported into the Database: " & lRecords
  148.  
  149. Exit_Here:
  150.    ' Cleanup all objects  (resume next on errors)
  151.    On Error Resume Next
  152.    Set wks = Nothing
  153.    Set wbk = Nothing
  154.    Set appExcel = Nothing
  155.    Set ExcelApp = Nothing
  156.    Set rst = Nothing
  157.    Set dbs = Nothing
  158.    DoCmd.Hourglass False
  159.    Exit Function
  160.  
  161. err_Handler:
  162.    ProcessFileImport = Err.Description
  163.    'Me.lblMsg.Caption = Err.Description
  164.    Resume Exit_Here
  165.  
  166. End Function
  167.  
  168.  
  169.  
Feb 2 '11 #7
ADezii
8,834 Expert 8TB
Rather than restructure the Code, which I think it really needs, I created a radically different approach. I Imported the entire applicable Range (B8:AG27) into a Temporary Table, then Appended specific Fields into CCTVPipeTemporary. Rather than go into specifics, download the Attachment and see if it can be of any use to you.

P.S. - Don't forget to change the Value of the Constant (conPATH) to point to your Excel Spreadsheet (For Me.xls).
Attached Files
File Type: zip Test.zip (21.2 KB, 98 views)
Feb 3 '11 #8
oh I can't open the file Test.zip. It says "the file is located outside your intranet or on an untrusted site"
Feb 3 '11 #9
ADezii
8,834 Expert 8TB
Talk to your Network Administrator who should be able to fix the problem.
Feb 3 '11 #10
ok I will try but I dont know how long it will take them to respond. But for now, Can you please put the information into another file like maybe a text file or Excel file. I think If you have set up a table in that Access file you can copy it into Excel and attach it here. Thank you.
Feb 3 '11 #11
ADezii
8,834 Expert 8TB
Expand|Select|Wrap|Line Numbers
  1. Dim strDate As String
  2. Dim MyDB As DAO.Database
  3. Dim rst_1 As DAO.Recordset
  4. Dim rst_2 As DAO.Recordset
  5. Dim ExcelApp As New Excel.Application
  6. Dim ExcelBook As New Excel.Workbook
  7. Dim wks As New Excel.Worksheet
  8.  
  9. 'Substitute you own PATH for For Me.xls
  10. Const conPATH As String = "C:\Stuff\For Me.xls"
  11.  
  12. DoCmd.Hourglass True
  13.  
  14. 'Import Errors on Fields F27 and F28 (irrelevant)
  15. CurrentDb.TableDefs.Delete "_ImportErrors"
  16.  
  17. 'DELETTE ALL Records from tblTemp Table and CCTVPipeTemporary
  18. CurrentDb.Execute "DELETE * FROM tblTemp;", dbFailOnError
  19. CurrentDb.Execute "DELETE * FROM CCTVPipeTemporary;", dbFailOnError
  20.  
  21. 'Retrieve the Date from Cells(3,3)
  22. Set ExcelBook = ExcelApp.Workbooks.Open(conPATH)
  23. strDate = ExcelBook.Worksheets("yyyy-mm-dd").Cells(3, 3).Value
  24.  
  25. ExcelApp.Quit
  26. Set ExcelApp = Nothing
  27.  
  28. 'populate a TEMP table (tblTemp with the entire Spreadsheet Range)
  29. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp", conPATH, False, "B8:AG27"
  30.  
  31. Set MyDB = CurrentDb
  32. Set rst_1 = MyDB.OpenRecordset("tblTemp", dbOpenForwardOnly)
  33. Set rst_2 = MyDB.OpenRecordset("CCTVPipeTemporary", dbOpenDynaset)
  34.  
  35. With rst_1
  36.   Do While Not .EOF
  37.     If Not IsNull(![F29]) And Not IsNull(![F30]) And Not IsNull(![F31]) And Not IsNull(![F32]) Then
  38.       rst_2.AddNew
  39.         rst_2![Field Measurement] = ![F29]
  40.         rst_2![Line Complete] = ![F30]
  41.         rst_2![Major Defects] = ![F31]
  42.         rst_2![Comments] = ![F32]
  43.         rst_2![Date] = strDate
  44.       rst_2.Update
  45.     End If
  46.       .MoveNext
  47.   Loop
  48. End With
  49.  
  50. DoCmd.Hourglass False
  51.  
  52. rst_1.Close
  53. rst_2.Close
  54. Set rst_1 = Nothing
  55. Set rst_2 = Nothing
  56.  
  57. 'Let's see the results
  58. DoCmd.OpenTable "CCTVPipeTemporary", acViewNormal, acReadOnly
  59. DoCmd.Maximize
Feb 3 '11 #12
Thanks, but unfortunately the TransferSpreadsheet() method fails for my Excel file just because of the way the Headings in blue text are. They are spread over two lines(rows 6 and 7) and the TransferSpreadsheet method doesn't like that and I am not allowed to change the spreadsheet. So I have to just stick to using loops to put in the data. With the loops Can I merge the two recordsets?
Feb 3 '11 #13
ADezii
8,834 Expert 8TB
@Ehsan - Doesn't the first Row to be processed begin at Row #8, as indicated by the 3rd Argument in the Function, namely:
Expand|Select|Wrap|Line Numbers
  1. ProcessFileImport(sOutput, "CCTVPipeTemporary", 8, 2, 1, 1)
Feb 3 '11 #14
Yes thats right, thats the row where the data values start, row 8. But nevertheless, the TransferSpreadsheet method looks also at the titles for each column and since the columns run over two rows(6 and 7) it generates an error. When using loops, I ignored reading the column titles and just read in the data from row 8 onward to get around that error. Thats why I decided to use For loops rather than the TransferSpreadsheet method.
Feb 3 '11 #15
ADezii
8,834 Expert 8TB
Use Generic Column Names and avoid the Column Headers as in the Sample Demo.
Feb 3 '11 #16
Thanks very much for the help. I figured out what was wrong with my code. It turns out I didnt need to use two recordsets, all I did was to use some If() then statements inside the first loop and it worked.
Feb 4 '11 #17
ADezii
8,834 Expert 8TB
Glad you worked it all out, how qabout poating the revised code?
Feb 4 '11 #18
Yeah sure. I am also thinking of displaying all contents under the "Date" column in one single messagebox so thats what I am working on now. I can display each in a different messagebox but I want to combine them all in one. If you have an idea on that please let me know.

Here is the revised code which comes after exiting the For loop in my initial post:

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.    iFld = iFld + 7
  4.    If iFld = 33 Then
  5.       rst("Date").Value = wks.Cells(3, 3)
  6.    End If
  7.    iFld = iFld - 1
  8.    If iFld = 32 Then
  9.       iCol = iCol + 5
  10.       rst("CCTV Inspection Comments").Value = wks.Cells(iRow, iCol)
  11.    End If
  12.    iFld = iFld - 1            'iFld = 31
  13.    If iFld = 31 Then
  14.       iCol = iCol - 1
  15.       rst("Major Defects(codes only)").Value = wks.Cells(iRow, iCol)
  16.    End If
  17.    iFld = iFld - 1            'iFld = 30'iCol = 31
  18.    If iFld = 30 Then
  19.      iCol = iCol - 1
  20.      rst("Line Completed?").Value = wks.Cells(iRow, iCol)
  21.    End If
  22.    iFld = iFld - 1
  23.    If iFld = 29 Then
  24.         iCol = iCol - 1
  25.         rst("Field Measurement (m)").Value = wks.Cells(iRow, iCol)
  26.    End If
  27.  
  28.  
  29.       iRow = iRow + 1
  30.       rst.Update
  31.       rst.AddNew
  32.  
  33.  
  34.    Loop
  35.  
  36.  
  37.  
Feb 4 '11 #19

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Phil Rutter | last post by:
Hello All, I have about 700 word documents that have 2 tables one is static 4 colums x 5 rows the other is 5 colums x rows ranging from 2 to 100 what i wolud like to do is open the word doc....
1
by: Dave | last post by:
I have a form which is based on a table (a) with over 6000 records.I have a button on this form which will open another form with related data from another table (b). What I want to do is open the...
0
by: VMI | last post by:
My windows app contains a process that loads data to a datatable. Is it possible to load this data into an Access table with exactly the same table structure? For example, if my process adds 100...
2
by: VMI | last post by:
I have a datatable with emplcodes and I need to extract, from my Access table, all records that contain an emplcode included in my datatable. In other words, I need to do a query that accesses a...
12
by: VMI | last post by:
For some reason, the process of retrieving data (about 20 records) from an Access table that has 400K records to a dataTable is taking over 3 mins. to complete. Below is my code to connect to the...
5
by: Doomster | last post by:
I have Office 2000. I am creating a database that will record stats from football, basketball, and hockey games. One table will contain info about a game. The game could be a football,...
0
by: NasirMunir | last post by:
I am trying to compare records from oracle table with a table in access. With oraDynaset, I have the option of .findNext function. I can use that to look for matching records from access to oracle....
15
by: OfficeDummy | last post by:
Hello all! I searched the Internet and this forum for a similar problem, but I found no help... Also, I'm a complete newbie to the fascinating world of programming, VBA and Access, so my question...
12
by: anand padia | last post by:
I have a master access table where we store all the employee information. I have various application developed in excel which imports and uses information in master. Now I want to develop a excel...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.