Excel Import and Replace Data in Table

[andy_dyer]

Ok...

I've found literally hundreds of posts both on this forum and on others but none appear to give me pointers for what I want to be able to do...

I have three Excel files

Task Import.xls
Data Import.xls
Project Import.xls

I have also three Access tables

tblTask
tblData
tblProject

The Excel files have headings and at present because only I has access to the tables I have to manually right click import and browse to the files (they are always in the same location) then click next, next, next, next, next and then type in the same table name as it currently exists and then click yes to overwrite...

It seems crazy because there are no options that need changing that I can't find a way of doing this either one at a time or all together preferbaly using vba or at worst a macro...

Here is where I am with one table - this deletes the current table then imports under the same table name but continues the auto numbering on the Primary Key - can I get this to reset somehow? And then do i just repeat the code for the other files or is there a better way??

Private Sub cmdImport_Click()

Dim msg As String, button As Variant, title As String, response As Variant
msg = "Is the updated file Task Import placed in 'Z:\PM Metrics\Task Import.xls'?"
button = vbYesNo + vbDefaultButton2
title = "File Location Checkpoint"

response = MsgBox(msg, button, title)
If response = vbYes Then
'Delete old records from tbltask
   DoCmd.SetWarnings False
   DoCmd.RunSQL "Delete [tblTask].* from [tblTask]"
   DoCmd.SetWarnings True

'Import new records from Excel file into tblTask
   DoCmd.TransferSpreadsheet acImport, 8, "tblTask", "Z:\PM Metrics\Task Import.xls", True, ""
Else
DoCmd.OpenForm "frmmenu"
End If

End Sub

Thanks everyone for looking!!

 

[Dennisk]

you shoud'nt be concerned about the autonumber as this guarantees unique numbers not consectutive numbers.

If you delete the contents of a table then compact the db, the autonumber will reset.

Another thing you need to provide is error trapping. Whenever you access external data there is always the chance that the drive may be be available etc.

One final avenue to expore with inports is creating your own specification, you can use this to specify date formats, whether the first line contains headings, and how you map the incoming fields to the fields in the import table.

 

[andy_dyer]

you shoud'nt be concerned about the autonumber as this guarantees unique nubers not consectutive numbers.

If you delete the contents of a table then compact the db, the autonumber will reset.

Another thing you need to provide is error trapping. Whenever you access external data there is always the chance that the drive may be be available etc.

One final avenue to expore with inports is creating your own specification, you can use this to specify date formats, whether the first line contains headings, and how you map the incoming fields to the fields in the import table.

Thanks Dennisk

I realise that about the numbers but as the data is literally replacing the existing data it is appropriate (I think) to reset the numbering otherwise doing this on a weekly basis for 20,000 records will quickly mean the autonumbers get silly.

Is there a way of automating the compacting of the database as part of the code? I'm desparately trying to hand this over to users so that I don't have to do any routine work on it...

I'm not great with vba so error handling is a complete unknown to me... I had to look up how to add the message box just a prompt and hope that this will avoid trying to run this without the files being in the right place...

If you can offer any pointers - I'm happy to improve my knowledge!

 

[Dennisk]

you can compact on close, and it may possibly be able to compact a closed db from an open one (not tried this). Just do'nt worry about the autonumbers as they should never be exposed to a user. try search this forum for members ideas on auto numbers. there are questions about this every week.

Finally what is silly about a long integer, the are meant to represent numbers approx +- 10 to the power of 38?

 

[editolis]

Try this:

Private Sub cmdImport_Click()

Dim msg As String, button As Variant, title As String, response As Variant
msg = "Is the updated file Task Import placed in 'Z:\PM Metrics\Task Import.xls'?"
button = vbYesNo + vbDefaultButton2
title = "File Location Checkpoint"

response = MsgBox(msg, button, title)
If response = vbYes Then
'Delete old records from tbltask
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete [tblTask].* from [tblTask]"
DoCmd.SetWarnings True

Application.SetOption ("Auto Compact"), 1
Application.SetOption "Show Status Bar", True
vStatusBar = SysCmd(acSysCmdSetStatus, "COMPACT DATABASE!")
docmd.quit

Else
DoCmd.OpenForm "frmmenu"
End If

End Sub


Then when you open again your database create o button to your form and put this code:
'Import new records from Excel file into tblTask
DoCmd.TransferSpreadsheet acImport, 8, "tblTask", "Z:\PM Metrics\Task Import.xls", True, ""

 

[andy_dyer]

Thanks for that editolis - it got me thinking... whether we could get the database to restart after compacting automatically...

I found this code elsewhere...

'-----------------------------------------------------------------------------
' Utilities from http://blog.nkadesign.com/microsoft-access/
' (c) Renaud Bompuis, 2008-2009
' Licensed under the Creative Commons Attribution License
' http://creativecommons.org/licenses/by/3.0/
' http://creativecommons.org/licenses/by/3.0/legalcode
'
' Free for re-use in any application or tutorial providing clear credit
' is made about the origin of the code and a link to the site above
' is prominently displayed where end-user can access it.
'-----------------------------------------------------------------------------

Option Compare Database
Option Explicit

' Time out set to 60 iterations, after which the batch file should delete itself
Private Const TIMEOUT = 60

'-----------------------------------------------------------------------------
' Restart the current application - v1.2 09AUG2008
' How does it work:
' First, we create a small batch file to which we pass the path and extension
' of the Access executable, the current database and its lock file extension.
' Then we run that script and close the application.
' The script monitors the presence of the lock file.
' As soon as Access removes the lock file:
' - if the Compact option was given, then we compact the database first
' - we open the database again.
' The script will delete itself automatically.
'
' For the batch file, some references:
' http://www.dx21.com/HOME/ARTICLES/P2P/ARTICLE.ASP?CID=12
' http://malektips.com/dos0017.html
' http://www.catch22.net/tuts/selfdel.asp
'-----------------------------------------------------------------------------
Public Sub Restart(Optional Compact As Boolean = False)
    Dim scriptpath As String
    
    ' Construct the full path of our temporary batch file
    scriptpath = Application.CurrentProject.FullName & ".dbrestart.bat"
    
    ' if the script already exists, then check it isn't an old remnant
    ' that has passed it's timeout.
    If Dir(scriptpath, vbNormal) <> "" Then
        If DateAdd("s", TIMEOUT * 2, FileDateTime(scriptpath)) < Date Then
            ' We've passed twice the batch file timeout, giving ample time for
            ' it to exectute, so if it's still there, it's most probably a dud
            Kill scriptpath
        Else
            ' Timeout hasn't expired beyond the acceptable limit, so it's probably
            ' still active, just try to close the application again
            Application.Quit acQuitSaveAll
            Exit Sub
        End If
    End If
    
    ' Construct the batch file
    ' Note that the TIMEOUT value is only used as a loop counter and
    ' we do not really count elapsed time in the batch file.
    ' The ping command takes some time to load and start and even though
    ' we set its timeout to 100ms, it will take much longer than that to
    ' execute.
    ' If we've been asked to comnpact the database, we launch the database
    ' using the /compact command line switch
    Dim s As String
    s = s & "SETLOCAL ENABLEDELAYEDEXPANSION" & vbCrLf
    s = s & "SET /a counter=0" & vbCrLf
    s = s & ":CHECKLOCKFILE" & vbCrLf
    s = s & "ping 0.0.0.255 -n 1 -w 100 > nul" & vbCrLf
    s = s & "SET /a counter+=1" & vbCrLf
    s = s & "IF ""!counter!""==""" & TIMEOUT & """ GOTO CLEANUP" & vbCrLf
    s = s & "IF EXIST ""%~f2.%4"" GOTO CHECKLOCKFILE" & vbCrLf
    If Compact Then
        s = s & """%~f1"" ""%~f2.%3"" /compact" & vbCrLf
    End If
    s = s & "start "" "" ""%~f2.%3""" & vbCrLf
    s = s & ":CLEANUP" & vbCrLf
    s = s & "del %0"
    
    ' Write batch file
    Dim intFile As Integer
    intFile = FreeFile()
    Open scriptpath For Output As #intFile
    Print #intFile, s
    Close #intFile
    
        
    ' Create the arguments to be passed to the script
    ' Here we pass it the full path to the database minus the extension which we pass separately
    ' this is done so that we can reconstruct the path to the lock file easily in the script.
    ' The extension to the lock file is also passed as a third argument.
    Dim dbname As String, ext As String, lockext As String, accesspath As String
    Dim idx As Integer
    
    ' Get the path to the msaccess executable, wherever that is
    accesspath = SysCmd(acSysCmdAccessDir) & "msaccess.exe"
    
    ' Find the extension, starting from the end
    For idx = Len(CurrentProject.FullName) To 1 Step -1
        If Mid(CurrentProject.FullName, idx, 1) = "." Then Exit For
    Next idx
    dbname = Left(CurrentProject.FullName, idx - 1)
    ext = Mid(CurrentProject.FullName, idx + 1)
    
    ' Depending on the database extension, determine its lock file extension
    If Left(ext, 2) = "ac" Then
        lockext = "laccdb"
    Else
        lockext = "ldb"
    End If
    
    ' Call the batch file
    s = """" & scriptpath & """ """ & accesspath & """ """ & dbname & """ " & ext & " " & lockext
    Shell s, vbHide
    
    ' Close our application
   Application.Quit acQuitSaveAll
End Sub

This is then called by:

Utilities.Restart Compact:=Nz(ckCompact, False)

The problem is that this appears to try and do something too quickly and i get the following error message:

"Could not use 'Z:\Project Metrics\Project Metrics -test.mdb'; file already in use"

Does that make sense to anyone??

I'd also like to be able to always compact and not rely on a newly created checkbox on my form called ckCompact which is required for this code... I don't understand the code enough to edit it...

 

[editolis]

For to open a database by code try this:

Application.FollowHyperlink "file://C:\file\file\file.mdb"

To tell you the truth i do not know how to AUTO Re-open a database by code.

 

[andy_dyer]

I've followed editolis suggestion and have the following code:

Private Sub cmdImport1_Click()

Dim msg As String, button As Variant, title As String, response As Variant
msg = "Is the updated file 'Task Import.xls' placed in 'Z:\PM Metrics\Task Import.xls'?"
button = vbYesNo + vbDefaultButton2
title = "File Location Checkpoint"

response = MsgBox(msg, button, title)
If response = vbYes Then
'Delete old records from tbltask
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete [tblTask].* from [tblTask]"
DoCmd.SetWarnings True

msg = "Is the updated file 'Project Import.xls' placed in 'Z:\PM Metrics\Project Import.xls'?"
button = vbYesNo + vbDefaultButton2
title = "File Location Checkpoint"

response = MsgBox(msg, button, title)
If response = vbYes Then
'Delete old records from tblproject
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete [tblProject].* from [tblProject]"
DoCmd.SetWarnings True

msg = "Is the updated file 'Data Import.xls' placed in 'Z:\PM Metrics\Data Import.xls'?"
button = vbYesNo + vbDefaultButton2
title = "File Location Checkpoint"

response = MsgBox(msg, button, title)
If response = vbYes Then
'Delete old records from tbldata
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete [tblData].* from [tblData]"
DoCmd.SetWarnings True

Application.SetOption ("Auto Compact"), 1
Application.SetOption "Show Status Bar", True
'Utilities.Restart Compact:=Nz(ckCompact, False)
vStatusBar = SysCmd(acSysCmdSetStatus, "COMPACT DATABASE!")
DoCmd.Quit

Else
DoCmd.OpenForm "frmmenu"
End If
End If
End If
End Sub

Private Sub cmdImport2_Click()
   DoCmd.TransferSpreadsheet acImport, 8, "tblTask", "Z:\PM Metrics\Task Import.xls", True, ""
   DoCmd.TransferSpreadsheet acImport, 8, "tblProject", "Z:\PM Metrics\Project Import.xls", True, ""
   DoCmd.TransferSpreadsheet acImport, 8, "tblData", "Z:\PM Metrics\Data Import.xls", True, ""

Dim msg As String, button As Variant, title As String, response As Variant
msg = "Import Complete"
button = vbOK
title = "Import Successful"

response = MsgBox(msg, button, title)
If response = vbOK Then
Else
End If

End Sub

I would really like to be able to get Access to Restart automatically like the previously pasted code is almost able to do (it achieves it on a 2nd attempt with the previously stated error on the first attempt - it's almost as though it's trying to do the restart too quickly...)

Can anyone make any sense of this??

 

[andy_dyer]

For to open a database by code try this:

Application.FollowHyperlink "file://C:\file\file\file.mdb"

To tell you the truth i do not know how to AUTO Re-open a database by code.

Hi editolis,

Thanks again - that idea didn't work and I can't make any sense of that complex code I've pasted...

 

[editolis]

Wait a couple of minutes.

I Think i found a solution to restart the DATABASE.

 

[editolis]

I Found this database on the internet.

I Think this is exactly what you are looking for.

Enjoy...

 

[andy_dyer]

I Found this database on the internet.

I Think this is exactly what you are looking for.

Enjoy...

That's the same one I found and pasted the code for earlier... I get the error message:

"Could not use 'Z:\Project Metrics\Project Metrics -test.mdb'; file already in use"

The 1st time it runs I click ok, it then tries again and it works but before I use it I need to work out why it doesn't work for me when it works for the sample database...


Thanks for your time looking editolis!

 

[editolis]

Now i think that you need help from a Microsoft Access MVP from this forum.

Also i suggest you to send e-mail to the developer of the RestartDatabase.

I Think he knows all the answers about your problem.

Good Luck...

 

[greebo]

Andy,

Re compacting databases, is your application split into a front-end and separate back-end data store? If yes, do you use access or SQL for the long term storage?

My clients have their front-end apps refreshed at every network logon (via a batch file). The master copies are saved in a secure location and their shortcuts point to a standard filename,e.g current master copy "userapp216.mdb" gets copied at logon to "userapp.mdb"

You can also schedule a batch file to run at a specific time (midnight!) to refresh the front end apps.

Re replacing data, I do this constantly in different apps and have had some excellent help on a problem today

basically I import data into a temp table, deleting the table after use, so I always create the table fresh each time

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
For Each tdf In dbs.TableDefs
If tdf.Name = "a1tblTempImport" Then
dbs.TableDefs.Delete tdf.Name
End If
Next tdf

Then I create the table again with:
Dim strPath as String
Dim strImpFile as String
strPath = "Z:\PM Metrics\"
StrImpFile="Task Import.xls"
DoCmd.TransferSpreadsheet acImport, 8, "a1tblTempImport", strPath & strImpFile, False, "month!a1:Ae"

The column headings are created as F1, F2, etc then I use standard append queries to move the data to where I want it.

To check the source file exists:
If Dir(strPath & strImpFile, vbDirectory) = "" Then
MsgBox "File " & strImpFile & " does not exist", vbCritical + vbOKOnly, "ERROR"
End If

Or am I missing the point completely? It is getting late in the day!

greebo

 

[andy_dyer]

Hi greebo,

No it's not split just all in one place... not clever enough to work out why and how anyone would split one anyway...

I'm working around this with previous suggestions which clear down my tables, then close and compact and then I manually reopen and import fresh data...

Not ideal but my users are getting used to it...

The owner of the code that was found that is supposed to achieve this has gone awol and has not responded to my request for help directly so unless anyone here has any brain waves...

Thanks anyway though...