importing using docmd.transferspreadsheet

[token_remedie]

Hey,

So I'm just brainstorming a little here and I need the foundation to be right which is where I've gone wrong in the past. I need a database to be able to read four dynamic spreadsheets but only one particular sheet of it, then filter or clean that data and ammend or add to an existing table in the db. So what I'm thinking is docmd.transferspreadsheet then dump that into a temporary table, compare serial numbers (it's an asset database) and if duplicate serial numbers then obviously it's an ammendment if no duplicate then it's a new entry and update the serial with the new details. clear the temp table so my forms and search functions all read from the one table. Do you think that's the best way?

 

[John Big Booty]

Perhaps the sample posted here will give you some ideas.

 

[token_remedie]

perfect! that was exactly what I needed. I've managed to get it to import into a table, and run a duplicate find query at the end of that code, now I'm thinking to get those duplicate entries back into the main table i need an append query yes?
I've got the query to run which looks for duplicate asset numbers between my main table and the freshly imported one. If it finds duplicates I want it to then look at the rest of the details of it, like location and if it's been disposed. Do I put that in the SQL of the first query with an iif? or do I create a new query based on the results of the first query that's run?

 

[John Big Booty]

perfect! that was exactly what I needed. I've managed to get it to import into a table, and run a duplicate find query at the end of that code, now I'm thinking to get those duplicate entries back into the main table i need an append query yes?
...

If you want that to appear as new record, then yes you will need an append query.

...
I've got the query to run which looks for duplicate asset numbers between my main table and the freshly imported one. If it finds duplicates I want it to then look at the rest of the details of it, like location and if it's been disposed. Do I put that in the SQL of the first query with an iif? or do I create a new query based on the results of the first query that's run?

I'm pretty sure that that sounds like the procedure I have in that sample to to identify existing vessels that are making a return trip under a new voyage number. Have a look at the series of Queries called QRY_NewVoy, and check if that's doing the sort of thing you want.

 

[token_remedie]

Tabletest is the import, Books is the main asset table matched assets is the query matching imports to the main table and transactions is a history table.

any idea where am I going wrong here?

Private Sub Command0_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String

DoCmd.SetWarnings False
path = Application.CurrentProject.path & "\Import"

'Loop through the folder & build file list
strFile = Dir(path & "*.xls")

While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend

'see if any files were found
If intFile = 0 Then
MsgBox "Could Not Find File"
Exit Sub
End If

'cycle through the list of files
For intFile = 1 To UBound(strFileList)
filename = Application.CurrentProject.path & "\" & strFileList(intFile)
'DoCmd.TransferText acImportDelim, , "YOURTABLENAME", filename, False

DoCmd.TransferSpreadsheet acImport, , "Tabletest", filename, True
Next intFile

DoCmd.SetWarnings True
DoCmd.OpenQuery "matchedtest"
'DoCmd.SetWarnings False
'DoCmd.RunSQL "DELETE * FROM Tabletest"


Dim SQL As String


'checks for duplicates between the two tables

SQL = "SELECT Books.* FROM Books LEFT OUTER JOIN matchedtest ON matchedtest.[asset number] = Books.[asset number] WHERE Books.[asset number] = IS NOT NULL"

If DCount("*", "Books", "Books.[asset number]") < 1 Then

'msg box if it finds it

MsgBox ("This asset has already been distributed")
Exit Sub
Else
MsgBox ("Not yet Distributed")

'string creation and insert


SQL = "INSERT INTO Transactions([asset number],[Asset Description],[serial no],[Invent No],[old location])SELECT Tabletest('" & [Asset Description] & "','" & [Serial No] & "','" & [Invent No] & "','" & Location & "') WHERE(((matchedtest.[asset number]) Is Not Null));"

End If


CurrentDb.Execute SQL





End Sub

 

[token_remedie]

actually i dont even want that stupid message box. all i really need to do is, if its a match from Tabletest then move that entry from books to transactions, delete it from books and reinsert the entry with the new details into books...