Importing from a shared drop box

[ssworthi]

I hope someone can help me. I am building a timekeeping db for a friend who is located in a different city; I'm explaining this so you understand the logistics.
I am one of 5 licenses for Accesses that the owner has, one being in their home office and we are linked by drop box.
Is there a way that they can import a file file through a macro to update the main employee tables? I created the macro and can run it from my desktop but their configuration is different and everything I have tried so far hasn't been successful. Is there a better way to do this; I am quite the amatuer compared to the users here.


The macro runs a delete query deleting all employee records, then runs an import and append using a file in the drop box. Would I create an import and have the user somehow point to the file that they can access, would that work?
Thanks for your help.

[Micron]

Judging by the lack of replies, I'm thinking you have not explained it as well as you think. I for one, do not understand "linked by a drop-box" nor the significance of the licenses. Sounds like you might have published your db on the web, or you could be using Share Point (because you guys are remote from each other?). You seem to think the differences in configuration is significant and you might be 100% right, but I have no idea what you mean by that. Are they running a Mac and you a PC??

That being said, consider exporting (pushing) the information in a manner they can use (spreadsheet, query result) at their end instead of importing (pulling). Nor would I recommend driving the process remotely unless you use transactions. If the connection breaks, you need to be able to roll back any record edits or deletions.

[ssworthi]

thank you for your response and yes I am terrible at composition, I am so sorry about that.
We share the data base using "drop box" service which allows us to share files instead of using a network.
The macro actually does create a .csv file for import into Quickbooks and I have defined where to "push" the file out to in the macro which is my access path to Dropbox. Unfortunately she has a different path so perhaps the solution is to create the macro that gives her the option of where to save it when she runs the macro? I have not tried this yet but I think that can be done. The macro currently runs export csv file then runs the query, then outputs the result to the dropbox. I hope this makes clearer sense.

[Micron]

Didn't mean to make you feel that you should apologize. Ok, if you're sharing the database (originally, by drop box I thought you were just using a term some people use when referring to a combo box control) then your idea seems to have merit. If you are actually using macros, I'm not sure how you'd do this other than reference a sub or function in a macro because I don't use macros, but if you're using code:
- a file path for each user login id could be stored in a table. If you already have a user table, maybe create a path field for it. If that would leave a lot of holes, a table for parameters is better (tblDbParams). You would need to get the user login id using the Environ(username) function or an API call.
- you would get the user preferred path based on the login id; could be as simple as using Dlookup
- adapt your macro or code to use the value you retrieve for the path and incorporate the path into the macro

If you used an input box to get the path, a macro will fail if the path typed in cannot be resolved because of one wrong press of a keyboard key
Another method of getting a file location is to use the File Dialog, which is preferred if the location will be different frequently.
Hope that helps.

[ssworthi]

You are using terminology way over my head, whew! Great stuff and I will research the "how to's" and "what it means" because I use minimal code and am just learning so bear with me. You mention use the "File Dialog"; can you explain that a little further, not sure I know what you mean.
My user will always be 300 miles away, so that won't change. I am guessing I can revise the macro to use the exact path she uses to get to the dropbox, is that what you are suggesting? Thanks so much for all your help. I learn so much from these challenges.

[ssworthi]

Judging by the lack of replies, I'm thinking you have not explained it as well as you think. I for one, do not understand "linked by a drop-box" nor the significance of the licenses. Sounds like you might have published your db on the web, or you could be using Share Point (because you guys are remote from each other?). You seem to think the differences in configuration is significant and you might be 100% right, but I have no idea what you mean by that. Are they running a Mac and you a PC??

That being said, consider exporting (pushing) the information in a manner they can use (spreadsheet, query result) at their end instead of importing (pulling). Nor would I recommend driving the process remotely unless you use transactions. If the connection breaks, you need to be able to roll back any record edits or deletions.

I am wondering if the issues my user is having is due to the data base permissions; that she can't run a macro that would update a table? Thank you

[Micron]

Please read this post carefully. To have a user file folder selection passed to code (or a macro I suppose) you'd call a custom function in the macro or code and it would return the choice. You'd then use that choice in place of the path that you would otherwise have to "hard code" in your macro/code. You can use this in conjunction with whatever you've already got, but allow the user to specify a place to keep whatever it is you are trying to store. The code for or choosing a file from a folder location is similar, but not exactly the same as what I posted below, which you can modify to suit. Calling the function is like this: if in code you are now saying 'path = "C:\blah\blah", you now say path=getFilePath (no quotes). Note that I am adding a "\" at the end of the path string. If you need to go a level further and pass the complete path - including the file name - you need to use the file dialog unless you append the file name to the value of vrtSelectedItem (see code). You will need to modify some of the settings such as ButtonName.

Function getFilePath() as String
Dim fd As FileDialog
Dim vrtSelectedItem As Variant

Set fd = Application.FileDialog(msoFileDialogFolderPicker)

With fd
    '.AllowMultiSelect = False
    .ButtonName = "Choose Folder"
    .Title = "Choose Scope Text Folder"
    .Filters.Clear
    '.Filters.Add "Text Files", "*.txt", 1
    '.InitialFileName =
    '.InitialView = msoFileDialogViewList
    If .Show = -1 Then
        vrtSelectedItem = .SelectedItems.Item(1) & "\" 'multi-select not allowed, so we can specify the item by index
        'The user pressed Cancel.
        Else
        Exit Function
    End If
End With

getFilePath = vrtSelectedItem    
End Function

Your issues are not clear to me. If you are having a problem with user permissions on the place the user is trying to save, or they cannot modify database tables due to permissions, this will not help.
One other comment at this point: You indicated in your pm that employee records are first being deleted, then re-built in this process. I wonder about that because it would generally be considered bad practice. If the rebuild process fails, then you have no records? There is a protection for this, but the first choice would be to not design so that this is a regular occurrence. Since you seem to be sharing compartmentalized data over a distance, perhaps you should read up on 'database replication'.

[ssworthi]

Please read this post carefully. To have a user file folder selection passed to code (or a macro I suppose) you'd call a custom function in the macro or code and it would return the choice. You'd then use that choice in place of the path that you would otherwise have to "hard code" in your macro/code. You can use this in conjunction with whatever you've already got, but allow the user to specify a place to keep whatever it is you are trying to store. The code for or choosing a file from a folder location is similar, but not exactly the same as what I posted below, which you can modify to suit. Calling the function is like this: if in code you are now saying 'path = "C:\blah\blah", you now say path=getFilePath (no quotes). Note that I am adding a "\" at the end of the path string. If you need to go a level further and pass the complete path - including the file name - you need to use the file dialog unless you append the file name to the value of vrtSelectedItem (see code). You will need to modify some of the settings such as ButtonName.

Function getFilePath() as String
Dim fd As FileDialog
Dim vrtSelectedItem As Variant

Set fd = Application.FileDialog(msoFileDialogFolderPicker)

With fd
    '.AllowMultiSelect = False
    .ButtonName = "Choose Folder"
    .Title = "Choose Scope Text Folder"
    .Filters.Clear
    '.Filters.Add "Text Files", "*.txt", 1
    '.InitialFileName =
    '.InitialView = msoFileDialogViewList
    If .Show = -1 Then
        vrtSelectedItem = .SelectedItems.Item(1) & "\" 'multi-select not allowed, so we can specify the item by index
        'The user pressed Cancel.
        Else
        Exit Function
    End If
End With

getFilePath = vrtSelectedItem    
End Function

Your issues are not clear to me. If you are having a problem with user permissions on the place the user is trying to save, or they cannot modify database tables due to permissions, this will not help.
One other comment at this point: You indicated in your pm that employee records are first being deleted, then re-built in this process. I wonder about that because it would generally be considered bad practice. If the rebuild process fails, then you have no records? There is a protection for this, but the first choice would be to not design so that this is a regular occurrence. Since you seem to be sharing compartmentalized data over a distance, perhaps you should read up on 'database replication'.

Sorry for not getting back sooner; I have been out of town with the holiday here, etc. During my trip I visited the user and in looking at her desktop, the actual program had not been installed completely causing all kinds of issues.
I appreciate your input and help and I think what I would like to learn more of is code; it seems that would help me out tremendously. Thanks again,