Improve company productivity with a Business Account.Sign Up

x
?
Solved

How do you update a value for a table when no join exist

Posted on 2011-09-12
7
Medium Priority
?
187 Views
Last Modified: 2012-05-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 ?

0
Comment
Question by:upobDaPlaya
  • 4
  • 2
7 Comments
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 36526843
Does this help?

Importing Standard spreadsheet template

HTH,

Kent
0
 

Author Comment

by:upobDaPlaya
ID: 36526869
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 ?
0
 
LVL 37

Accepted Solution

by:
Norie earned 2000 total points
ID: 36526875
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.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 

Author Comment

by:upobDaPlaya
ID: 36526889
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.
0
 
LVL 37

Assisted Solution

by:Norie
Norie earned 2000 total points
ID: 36526967
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.
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

Open in new window

You could also add a field (Yes/No) for to the filename table to indicate if a file has been imported or not.
0
 

Author Comment

by:upobDaPlaya
ID: 36526989
Thanks imnorie...you are correct I do not need a join, but since I was unsure on how to proceed with the SQL I needed some quidance on the SQL...

Let me give this try and I will let you know how this works...Thanks for your patience on this one,,,its appreciated
0
 

Author Closing Comment

by:upobDaPlaya
ID: 36945874
Thanks imnorie this is the direction I needed..
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question