473,327 Members | 1,920 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,327 software developers and data experts.

Looping through a table in Access using VBA

DJRhino1175
221 128KB
This is the link to what got me to the point I'm at now with the help from the great people of this forum:

https://bytes.com/topic/access/answe...command-button

Phil suggested I create a table then loop through the table, my issue is I do not know how to do that through code.

I made a table Called "DBUpdater" with 2 columns Col 1 is ID and Col 2 is "Sendto

Col 1 is an auto number
Col 2 is short text

I entered in col 2 "Assembly OEE Database-(Name)" - This is what the file needs to be renamed to.

I'm not sure where I need to go from here.

Here is the code I have so far to excute just one file:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCopy_Click()
  2.     Dim strPath     As String
  3.     Dim strFromFile As String
  4.     Dim strToFile   as String
  5.  
  6.     strPath = "G:\Unsecure-Share\ASM OEE Files\"
  7.     strFromFile = strPath & _
  8.         "Data Tables\Assembly OEE Database-Admin.accdb"
  9.     strToFile = strPath & _
  10.         "Assembly OEE Database-Brian Pritchett.accdb"
  11.  
  12.     Call FileCopy(strFromFile, strToFile)
  13. End Sub
Jul 25 '18 #1

✓ answered by twinnyfo

Great job DJ!

Just a few pointers--some are pure preference, some are more helpful.

First, I always recommend to adopt a consistent, nested indenting plan. This allows others (as well as yourself) the ability to see which nested expressions are related to each other (this is helpful).

Second, when opening a recordset, it is always best to verify that there are records before you start trying to manipulate the recordset and using its data (which can throw an error). This is required, in my opinion.

Third, I like to perform all necessary string preparations prior to opening my recordsets. This keeps the clutter out of the way (this is just preference).

Please see below for a few tweaks that may be useful to you in the future:

Expand|Select|Wrap|Line Numbers
  1. Private Sub DatabaseUpdate_Click()
  2.     Dim strPath     As String
  3.     Dim strFromFile As String
  4.     Dim strToFile   As String
  5.     Dim db          As DAO.Database
  6.     Dim rstFileList As DAO.Recordset
  7.  
  8.     strPath = "G:\Unsecure-Share\ASM OEE Files\"
  9.  
  10.     strFromFile = strPath & _
  11.         "Data Tables\Assembly OEE Database-Admin.accdb"
  12.  
  13.     Set db = CurrentDb
  14.     Set rstFileList = db.OpenRecordset("DBUpdater", dbOpenDynaset)
  15.     With rstFileList
  16.         If Not (.BOF and .EOF) Then
  17.             rstFileList.MoveFirst
  18.             Do While Not .EOF
  19.                 strToFile = strPath & rstFileList!SendTo
  20.                 Call FileCopy(strFromFile, strToFile)
  21.                 .MoveNext
  22.             Loop
  23.         End If
  24.         .Close
  25.     End With
  26.     db.Close
  27.     Set rstFileList = Nothing
  28.     Set db = nothing
  29. End Sub
Hope this hepps!

6 2895
twinnyfo
3,653 Expert Mod 2GB
DJ,

It's as simple as opeingin that table in a recordset and using the value of the "SendTo" path to change lines 9-10 above.

I say "simple" but I can't recall your expertise in building recordsets in VBA.....
Jul 25 '18 #2
Seth Schrock
2,965 Expert 2GB
After reading the other thread, I've got a question for you. Are you copying the same file to multiple locations (in other words, does strFromFile remain the same) or are there multiple sources going to multiple destinations?

To loop through a table, you need to define what is called a Recordset
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rstFileList As DAO.Recordset
  3.  
  4. Set db = CurrentDb
  5. Set rstFileList = db.OpenRecordset("DBUpdater", DbOpenDynaset)
  6.  
This sets up the table for your code to be able to see it and loop through it. Now you want to use a Do While loop to go through all the records and edit your strToFile variable and then call the FileCopy() function.

Expand|Select|Wrap|Line Numbers
  1. rstFileList.MoveFirst   'Makes sure that you are starting at the beginning of the table
  2. Do While Not rstFileList.EOF  'looks for the End Of File in the table
  3.     strToFile = strPath & rstFileList!SendTo   'Gets the value in the SendTo field for the current record 
  4.                                                '  and pastes it to the end of the strPath variable
  5.  
  6.     Call FileCopy(strFromFile, strToFile)
  7.  
  8.     rstFileList.MoveNext  'moves to the next record
  9. Loop
Jul 25 '18 #3
DJRhino1175
221 128KB
twinnyfo - I have very little experience in recordsets.

Seth Schrock - The from file will always be the same, its the send to that will change.

I will input this code in and see what happens, also I thank you very much for your input.
Jul 25 '18 #4
DJRhino1175
221 128KB
Here is the final code - Tested and works. Just incase someone else may need it.

Expand|Select|Wrap|Line Numbers
  1. Private Sub DatabaseUpdate_Click()
  2.  
  3.     Dim strPath     As String
  4.     Dim strFromFile As String
  5.     Dim strToFile   As String
  6.     Dim db As DAO.Database
  7.     Dim rstFileList As DAO.Recordset
  8.  
  9.     Set db = CurrentDb
  10.     Set rstFileList = db.OpenRecordset("DBUpdater", dbOpenDynaset)
  11.  
  12.     strPath = "G:\Unsecure-Share\ASM OEE Files\"
  13.  
  14.     strFromFile = strPath & _
  15.         "Data Tables\Assembly OEE Database-Admin.accdb"
  16.  
  17.         rstFileList.MoveFirst   'Makes sure that you are starting at the beginning of the table
  18.  
  19.     Do While Not rstFileList.EOF  'looks for the End Of File in the table
  20.  
  21.         strToFile = strPath & rstFileList!SendTo   'Gets the value in the SendTo field for the current record
  22.                                                '  and pastes it to the end of the strPath variable
  23.  
  24.     Call FileCopy(strFromFile, strToFile)
  25.  
  26.        rstFileList.MoveNext  'moves to the next record
  27.     Loop
  28.  
  29. End Sub
Again thank you so very much for your help Seth and twinnyfo
Jul 25 '18 #5
twinnyfo
3,653 Expert Mod 2GB
Great job DJ!

Just a few pointers--some are pure preference, some are more helpful.

First, I always recommend to adopt a consistent, nested indenting plan. This allows others (as well as yourself) the ability to see which nested expressions are related to each other (this is helpful).

Second, when opening a recordset, it is always best to verify that there are records before you start trying to manipulate the recordset and using its data (which can throw an error). This is required, in my opinion.

Third, I like to perform all necessary string preparations prior to opening my recordsets. This keeps the clutter out of the way (this is just preference).

Please see below for a few tweaks that may be useful to you in the future:

Expand|Select|Wrap|Line Numbers
  1. Private Sub DatabaseUpdate_Click()
  2.     Dim strPath     As String
  3.     Dim strFromFile As String
  4.     Dim strToFile   As String
  5.     Dim db          As DAO.Database
  6.     Dim rstFileList As DAO.Recordset
  7.  
  8.     strPath = "G:\Unsecure-Share\ASM OEE Files\"
  9.  
  10.     strFromFile = strPath & _
  11.         "Data Tables\Assembly OEE Database-Admin.accdb"
  12.  
  13.     Set db = CurrentDb
  14.     Set rstFileList = db.OpenRecordset("DBUpdater", dbOpenDynaset)
  15.     With rstFileList
  16.         If Not (.BOF and .EOF) Then
  17.             rstFileList.MoveFirst
  18.             Do While Not .EOF
  19.                 strToFile = strPath & rstFileList!SendTo
  20.                 Call FileCopy(strFromFile, strToFile)
  21.                 .MoveNext
  22.             Loop
  23.         End If
  24.         .Close
  25.     End With
  26.     db.Close
  27.     Set rstFileList = Nothing
  28.     Set db = nothing
  29. End Sub
Hope this hepps!
Jul 25 '18 #6
zmbd
5,501 Expert Mod 4TB
> Please see the alternative approach that I posted to your other thread. This method completely avoids the table, recordsets, etc...
https://bytes.com/topic/access/answe...on#post3817807
Jul 26 '18 #7

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

Similar topics

1
by: Jim Lawton | last post by:
I'm not sure where my problem lies, but someone here might have a suggestion. I'm running MySQL 4.1.7-nt on my laptop, and I'm accessing it through ODBC (MyODBC-3.51.10-x86-win-32bit). I'm...
1
by: Guinness Mann | last post by:
Greetings, I'm working in VS2003.NET using C#. I need to download a few rows from an SQL Server database table and then be able to page back and forth through them. I don't need to make any...
0
by: Jeff D. Hamann | last post by:
Sorry for the seemingly novice posting, but I could find a solution for this on the web so far... I've been developing a database using postgresql (and loving it) and have started running into...
0
by: Jeff D. Hamann | last post by:
Sorry for the seemingly novice posting, but I could find a solution for this on the web so far... I've been developing a database using postgresql (and loving it) and have started running into...
2
by: sachin2040 | last post by:
hi, i m trying to get a pivot table generated programmatically in ms access using vb, kindly help me. its really urgent.
2
by: acw | last post by:
On a SQL Server 2000 db I would like to setup a stored procedure that accesses couple tables and runs the extended stored procedure xp..cmdshell. The goal is to grant users with limited privileges...
3
by: deneushasler | last post by:
Hello my name is Juan Jose. My problem is as follows. When I try to insert a record into a table (access) to control DetailsView Visual Web Developer 2005, when I run the page and insert a record...
1
by: vivek samantray | last post by:
I have a query.When i try to create a index on one of the table the index gets created but when i take the output it stuill shows "TABLE ACCESS FULL" Please see below what i did QUERY ======...
0
by: KyzPu | last post by:
Hi there! I am making a database and I want to add columns to an existing table (tbl1) using the records from another table (tbl2) with VBA. For example: the first added column (tbl1) should be...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
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: 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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.