Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How do you update a value for a table when no join exist
Posted on 2011-09-12
Step 1 - Via MS Access VBA I am looping thru a file directory grabbing all the file names (all MS Excel files) and file paths found and placing them (file names and file paths) in a table call File-Names
Step 2- Still within the loop I then import the MS Excel file into a table called Excel-Import-Data, based on the File Name and File Path found in Step 1.
My quandry is when I import the Excel file into my table called Excel-Import-Data I want to update a field call File Name. However, since there is no common field/join between the tables Excel-Import-Data and File-Names I am unable tu update the field called File Name.
However, since I am looping thru my directory one file at a time while subsequently doing the import there must be a way to update the field called File Name within the table Excel-Import-Data ?
Since in the MS Access VBA I am using the docmd command.... to append and update records that may be my shortcoming..Probably there is a way in using a SQL command where I could achieve my goal, but all the Google searches I did on trying to find out on how to achieve this update required the use of a JOIN statement ?
Step 2- Still within the loop I then import the MS Excel file into a table called Excel-Import-Data, based on the File Name and File Path found in Step 1.
My quandry is when I import the Excel file into my table called Excel-Import-Data I want to update a field call File Name. However, since there is no common field/join between the tables Excel-Import-Data and File-Names I am unable tu update the field called File Name.
However, since I am looping thru my directory one file at a time while subsequently doing the import there must be a way to update the field called File Name within the table Excel-Import-Data ?
Since in the MS Access VBA I am using the docmd command.... to append and update records that may be my shortcoming..Probably there is a way in using a SQL command where I could achieve my goal, but all the Google searches I did on trying to find out on how to achieve this update required the use of a JOIN statement ?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
2
7 Comments
Hi Kent,
After looking at your link I still need to somehow take the variable sOpenFile and within my import table update the field call file name with the value found in the variable called sOpenFile.... Any ideas ?
After looking at your link I still need to somehow take the variable sOpenFile and within my import table update the field call file name with the value found in the variable called sOpenFile.... Any ideas ?
Active today
Why not just add a field to the Import-Excel-Data table for the file name?
Then once you've imported each file update that field for all the records imported to the filename they've come from.
That should be straightforward but I think more information on how you are importing would help.
All you've mentioned is DoCmd but not which command or what/where you are importing to.
Then once you've imported each file update that field for all the records imported to the filename they've come from.
That should be straightforward but I think more information on how you are importing would help.
All you've mentioned is DoCmd but not which command or what/where you are importing to.
imnorie,
Thats exactly what I am trying to do..."Then once you've imported each file update that field for all the records imported to the filename they've come from."
However, once I have imported the file what SQL is required to update the Import-Excel-Data table with the Filename. Within VBA I have always done my updates via the docmd. In this case an update query using the docmd command is not possible since any query I attempt would lack a join between the Import-Excel-Data table and the File-Names table.
Thats exactly what I am trying to do..."Then once you've imported each file update that field for all the records imported to the filename they've come from."
However, once I have imported the file what SQL is required to update the Import-Excel-Data table with the Filename. Within VBA I have always done my updates via the docmd. In this case an update query using the docmd command is not possible since any query I attempt would lack a join between the Import-Excel-Data table and the File-Names table.
Active today
If you are using the filename to import the data why do you need a join to get it from the table?
That's why I asked for more information on the import process.
This worked for me with the filenames in a field called XLFileName in a table caled tblImportFiles.
That's why I asked for more information on the import process.
This worked for me with the filenames in a field called XLFileName in a table caled tblImportFiles.
Option Compare Database
Option Explicit
Sub ImportXLData()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strFilename As String
Dim strPath As String
Dim strSQL As String
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT XLFileName FROM tblImportFiles")
rst.MoveFirst
strPath = "C:\Test\"
While Not rst.EOF
strFilename = rst!XLFileName
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Excel-Import-Data", strPath & strFilename, True, "Data$"
strSQL = "UPDATE [Excel-Import-Data] SET [Excel-Import-Data].fldSourceFile = '" & strPath & strFilename & "' WHERE fldSourceFile Is Null"
db.Execute strSQL
rst.MoveNext
Wend
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
Featured Post
Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page.
The problem with sub-reports and headings:
Add a dummy group to the sub report using the expression =1:
Set the “Repeat Section” property of the dummy…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month6 days, 17 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.