working with imported data

[token_remedie]

Hi, I need some help with a query after I've imported data into Access.
I've sucessfully imported the data locally but I need to be able to do it across a network, and once this is done, i want my query to:

* match data by asset number in the new table to asset number in the existing main table (books)
* if it finds a match copy that record from books to transactions(a history table)
* delete the record from books and insert into from the newly imported table

and do that for every record that's imported into the new table. I'll then be clearing the new table ready for next time it imports.

here's my code:

<code>

Option Compare Database

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" locally works
path = "Z:\Technologies\Projects\" ' network path that I'm trying to get working

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

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" 'query that finds matches between new table and existing main table
'DoCmd.SetWarnings False
'DoCmd.RunSQL "DELETE * FROM Tabletest"


Dim SQL As String

'here's where it's coming unstuck

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("*", "matchedtest", "Books.[asset number]") < 1 Then

'msg box so i know if the code is working

MsgBox ("nothing to update")
Exit Sub
Else
MsgBox ("updating")

'string creation and insert


SQL = "INSERT INTO Transactions([asset number])VALUES ('" & [Asset Number] & "')"

End If


CurrentDb.Execute SQL





End Sub


</code>


Thank you in advance if you can tell me where I'm going wrong

[June7]

What is your question? What is the issue - error message, wrong results, nothing happens?

BTW, use [] for CODE tags instead of <>.

[token_remedie]

well firstly, how do i get it to import across a network instead of locally?

and then after that data is imported im trying to compare by asset number to an existing table to tabletest is my imported data, and books is my main table then if it exists in books copy that record to transactions, delete it from books and insert the record from testtable to books, so that there's only one asset in books but its history is kept in transactions.

thanks for the code[] tip, I always wondered why it never works for me haha

[June7]

By network do you mean your office LAN? Can you map a drive designation to the location with Windows Explorer? Then should be simply naming that path in the code as you are doing. Actually, I prefer to use UNC (Universal Naming Convention). Instead of referring to location with drive letter, I use the server name, like: servername\\folders path\filename

Again, need specifics. What is not working - error messages. Have you step debugged? What line errors?

[token_remedie]

yeah across LAN,

I got it to import by using full UNC name, which is great but it doesn't actually point where it's supposed to it's redirecting to the desktop.

I put in MsgBox ("import" + filename + "?") to see what file name it's importing and it's pulling it from the desktop instead of the LAN location.

if I rename or remove that file from my desktop it still asks if i want to import it and then says it can't find the file... so where is that filename stuck? :S

[token_remedie]

oh pffft nevermind i found it

[token_remedie]

ok so I decided to go a different way and I think it's way better (for everyone's sake) because if I'm importing I can select the file myself.... and if I leave here someone can easily take over.

but I can't pass the filename to the import, any ideas?

Option Compare Database

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
    Dim f As Object
    
    Set f = Application.FileDialog(3)
        f.AllowMultiSelect = True
        f.show
    MsgBox "file chosen = " & f.selectedItems.Count
    

    DoCmd.SetWarnings False
    
        filename = f.selectedItems.Count
        'DoCmd.TransferText acImportDelim, , "YOURTABLENAME", filename, False
        MsgBox ("import" + filename + "?")
        
      DoCmd.TransferSpreadsheet acImport, , "Tabletest", filename, True

    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("*", "matchedtest.[asset number]", "Books.[asset number]") < 1 Then

'msg box if it finds it

MsgBox ("nothing to update")
Exit Sub
Else
MsgBox ("updating")

'string creation and insert


SQL = "INSERT INTO Transactions([asset number])VALUES ('" & [Asset Number] & "')"

End If


CurrentDb.Execute SQL





End Sub

it's just saying it doesn't exist when I try to import it

[June7]

Not understanding much of this code.

Why multi-select for the FileDialog? You declare a path variable but don't see it getting set and used. You are setting the filename variable to a Count value, not a file name. The DCount expression doesn't make sense to me.

[token_remedie]

i cleaned it up, and got it working with this:

Private Sub Command0_Click()
    Dim fd As FileDialog
    Dim strfile As String
    Dim strsearchpath As String
    Dim vrtSelectedItem As Variant
    
 Set myDialog = Application.FileDialog(msoFileDialogOpen)
 'Use strSearchPath  if you want to start in a particular directory. You can leave it out if you wish (as I have it commented out right now) and it will start with a default directory of Windows choosing.
 strsearchpath = "c:\"
 
 With myDialog
 
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xls", 1
     .Title = "locted of files"
     .InitialFileName = strsearchpath
     
DoCmd.SetWarnings False

If .Show = True Then
    For Each vrtSelectedItem In .SelectedItems
     MsgBox "file chosen = " & vrtSelectedItem
       ' pass the file information to another routine that handles the transfer
      DoCmd.TransferSpreadsheet acImport, , "Tabletest", vrtSelectedItem, True, "Master!"
    Next
    
    Else
    MsgBox "cancelled"
    
    End If
    End With
     
 
 End Sub

I kind of need it to force the import now or include some sort of error handler to put up a msgbox for what's happening if it's broke.

the next part is going to be cleaning up the imported data with sql...not looking forward to that part :S