Code to Import Text or Excel file 2

[shelby55]

Hi

I am using Access 2002 and a newbie at VBA.

I have a database that will be updated monthly as new data is available. But I want whatever data is in the file being updated to overwrite what is in the table.

In other words, if I am uploading Apr to Sep 2010 data then I want the Apr to Aug data in the table to be overwritten with the newer data.

What would my code look like? Thanks.

 

[dhookom]

Your code depends on your table structures. Can we assume you have adequate primary key fields in the source and target tables?

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi

Yes, sorry for not stating that. The combination of instno, chartno, and acctno make a unique id primary field.

 

[dhookom]

I would create an update query that joins the tables and updates the appropriate fields. You can use a LEFT or RIGHT join that includes all of the records from your new data table to append new records as well as update existing.

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi

Thanks very much....I'll let you know how it goes!

 

[shelby55]

Hi

This is great for when the data has already been imported into Access but what about the actual importing of it? Also, how can I use the import specs I already created for the button for the user to select and import the data?

Thanks.

 

[dhookom]

You might want to check out Ken Snells resources at Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi

Thanks Duane but I actually found that importing from the text file made more sense. I was able to create import specs....can they be referenced in code?

What I want the button to do is to have the dialog box open up for the user to selected the text file they wish to import, import it and update to the current table i.e. if there are records with the unique index key of instno, chartno and acctno then overwrite them.

Can this all be done with one button/set of code? Thanks.

 

[dhookom]

This can all be done with code. Check Help for "DoCmd.TransferText Method"

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Thanks Duane...I guess I better get learnin'!!

 

[shelby55]

Hi Duane

I've been searching for awhile now and can't find what I need to get what I want done in VBA. I've seen lots of post who suggest the best way to do it but not spelled out because, I assume, the person asking the question knew more about VBA than I do....

I understand that you want me to use docmd.transfertex and I understand that criteria #2 of this is where I enter the specifications that I created on import. But how do I get the dialog box to show up for the user to select the file because it seems that docmd.transfertext wants me to "hard code" this path?

Also, do I need to create the temporary table that I'm going to import this data to first before naming it in the DoCmd? What code do I use to run an update query?

Thanks.

 

[dhookom]

You can find code to browse for a file at Access Web APIs: Index of Articles

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi

Thanks but I still can't find what I need....but being new to VBA maybe I don't know what I need to be looking for.

I'd appreciate it if you could at least get me started with the code? Thanks.

 

[PHV]

but being new to VBA
Really ?
You have VBA related threads here since 16 Aug 03 ...

 

[shelby55]

Hi

Most of my VBA to date has been in Excel....I haven't used Access VBA in a very long time (probably since 2003).

Not sure what the point of your post was, if you don't wish to help, fine, I just figured that was what this forum was for. I'm not asking for the code outright, I just want to be pointed in the direction of what I'm looking for.....

 

[MaxieMax]

Shelby,
I have included below a section of script that I have taken from one of my forms.... some of it you won't need...

it uses the file dialog to open a file select window... you will see there are some filters and things on there....

you should be able to work out the bits you need from it.

hope it helps

Max



Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strfilename As String
Dim strsql As String
Dim fDialog As Office.FileDialog
Dim varFile As Variant

Set db = CurrentDb
strsql = "delete * from Atlantis"
db.Execute strsql
' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Allow the user to make multiple selections in the dialog box.
.AllowMultiSelect = True
' Set the title of the dialog box.
.Title = "ATLANTIS FILES!!"
' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Comma Seperated", "*.CSV"
.Filters.Add "Text", "*.txt"
.Filters.Add "All Files", "*.*"
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
' Loop through each file that is selected and then add it to the list box.
For Each varFile In .SelectedItems
'check file not already been loaded
strsql = "select * from filelog where filename='" & varFile & " '"
Set rs = db.OpenRecordset(strsql, dbOpenDynaset)
'If file not already loaded start load process
If rs.EOF Then
DoCmd.Hourglass (True)

DoCmd.TransferText acImportDelim, "Atlantis", "Atlantis", varFile
strsql = "select name from MSysobjects where name like '*_importErrors'"
Set rs = db.OpenRecordset(strsql, dbOpenDynaset)
Do While Not rs.EOF
DoCmd.DeleteObject acTable, rs!Name
rs.MoveNext
Loop
rs.Close

Set rs = db.OpenRecordset("Filelog", dbOpenTable)
With rs
.AddNew
rs!Filename = varFile
rs!load_Date = Now()
.Update
End With
rs.Close
Set rs = Nothing
DoCmd.Hourglass (False)
Else
'notify user file already loaded
MsgBox "The Atlantis File " & varFile & "Has Already Been Loaded", vbInformation
End If

Next
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With

Set fDialog = Nothing
db.Close
Set db = Nothing

End Sub

 

[shelby55]

Hi MaxieMax

Thank you so much for your awesome reply! I'm sure I'll find what I need in your code but hopefully you don't mind if I have questions after I've reviewed more closely.

Thanks again!

 

[MaxieMax]

Shelby,

No worries

 

[shelby55]

Hi Max

Sorry I'm just getting to this now but I changed all references to "Atlantis" and your files to see what the code would do (and therefore know what to include/exclude) but the code is stopping at "db as DAO.Database" indicating a "compile error: user-defined type not defined".

Why would that be a problem? Thanks.

 

[MaxieMax]

Hey Shelby,

Just noticed your only using Access 2002.... try removing the 'DAO.' as this is sometihng that is needed in 2003 onward...

Dim db As DAO.Database
Dim rs As DAO.Recordset

apart from that pretty much everything is the same.

Cheers

M