Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138

    Save imports


    I have two save imports that is working on one computer, in d:, but when I run the access file on my other computer in c:
    It does give error, because it is not on the same drive, is there a way that save import can work on both of themsn work on both of them.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Hi Hendrik,
    Can you give us a little more info? Where are these computers -- apparently not on same LAN?
    You are trying to use the same file(s) from 2 different computers, right?
    You need this file(s) to be available to 2 separate computers at the same time?
    You could move or copy the file to a named location eg. C:\users\documents\TheSharedFile
    that is the same on both computers.

    Good luck.

  3. #3
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138
    Quote Originally Posted by orange View Post
    Hi Hendrik,
    Can you give us a little more info? Where are these computers -- apparently not on same LAN?
    You are trying to use the same file(s) from 2 different computers, right?
    You need this file(s) to be available to 2 separate computers at the same time?
    You could move or copy the file to a named location eg. C:\users\documents\TheSharedFile
    that is the same on both computers.

    Good luck.

    Hi orange how are you.

    Yes it is two different computers, one at work and one at home. Using the same files on both of them.
    One at work is on d: not allowed to put stuff on c:.
    At home only c: is available. I did think to do same location ,but I cannot, that is why I want to know if there could be a other why to do it.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Are you running the same code/vba in both locations?
    You could have a variable in the code to identify LocationOfUse with value either Home or Work

    Code:
    If LocationOfUse = "Work" then
    FileLocation = "D:\....your file path on workPC"
    else
    FileLocation = "C:\... your filepath on HomePC"
    end if
    open file at FileLocation.

    Or you could use a FileDialog to browse to the proper folder and get the file for processing if you need something more flexible.

    Is your underlying issue knowing how to adjust the filepath in vba? I think the variable for work or Home would be easiest to do.

  5. #5
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138
    Orange

    Thank for the info
    I am not sure where to put this code must I put it the button that import the info, if so (I am trying to understand this) how will the save import work, i import about every day new data, so it work only in the one I have save (location).

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tell me more about the daily import process.
    Do you record the date on each record when you import?
    Do you accumulate import data in the same file?
    Do you process the imported data on a daily (same day) basis?
    Do you verify/validate the imported data? If so, how do you handle/correct/discard "failures/typos" etc?

    It seems the issue is more than where the file is located. Can you overview the big picture to show where the importing fits; who uses it etc.?

  7. #7
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138
    Orange,

    The import is working very well, at work no problem with it, only at home because the path is different for the "save imports" that i have done.
    I have save the imports to make a easier and faster to import, but I can do it the long way every time, but it takes time.

    See screenshot of my save imports
    Click image for larger version. 

Name:	NvnaMnQuO3.png 
Views:	14 
Size:	11.3 KB 
ID:	39733

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  9. #9
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138
    The screenshot is about 2 of my imports of tables, I have save them to the names that are showing.

    They are working at work, but at home not, because the path is not the same.
    I want to know if there is a way to make it so that I can use it at work and home (the same file) without to make a new import every time.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ??You have an imported file at work. Let's call it TheFile and it is stored at D:\html\Database\

    You have a separate copy of the same file TheFile at home and it is being stored on C:\......

    You could import TheFile to a USB stick, then take the USB stick to work or home and read the file from a USB port on the corresponding PC.

    I have a suspicion that we are on different wavelengths and not talking about the same thing????

  11. #11
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138
    I am doing that, I am copied the file(thefile) at work on a usb stick at work, then I copied the file(thefile) from the usb stick to my pc at home.
    Now in my access file I have the two import files that is link to "thefile" on my work pc, at home the path is not the same.
    This is where I want to know how to make the "save import" to work on both pc's, without every time to make a new import from excel file to access

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you just plug the USB stick into your home PC and access the file directly on the USB stick without copying if to C:\ something?
    Do you have to write/edit the file that is on the USB stick, or just read it?
    If your work PC is set up as you need, and your home PC is for testing/experimenting, then you could put some code in your home version of the program to select the USB or some path on C:\ by using a FileDialog approach.

    Steve ssanfu on this forum has many examples of FIleDialog ---using Search with these criteria keyword filedialog and username ssanfu

    More info on FileDialog

  13. #13
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138
    Hl orange
    Went to bed and started to think about thisa,and I have a plan. I will make a new import button to show path at home. So one for work and one for home, but thank you for helping me with this.

  14. #14
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by hendrikbez View Post
    Hl orange
    Went to bed and started to think about thisa,and I have a plan. I will make a new import button to show path at home. So one for work and one for home, but thank you for helping me with this.
    Hi Hendrik!

    Your plan is a very good solution, but, you could do something more automated.
    In a standard code module, add this function:
    Code:
    Function MyImportsDrive() As String
        Dim IES As ImportExportSpecification
        Dim strDrive As String
    
        If Environ("computername") = "MyHomeComputer" Then
            strDrive = "c:\"
            For Each IES In CurrentProject.ImportExportSpecifications
                IES.XML = Replace(IES.XML, "d:\", strDrive)
            Next IES
        Else
            strDrive = "d:\"
            For Each IES In CurrentProject.ImportExportSpecifications
                IES.XML = Replace(IES.XML, "c:\", strDrive)
            Next IES
        End If
        MyImportsDrive = strDrive
    End Function
    (replace the red part with the actual name of your home computer)

    Then, in the "On Load" event of the first form of your Access file, add these lines of code:
    Code:
    MsgBox "You are working on " & Environ("computername") _
               & " computer and now your saved imports refers to " _
               & MyImportsDrive & " drive.", vbInformation
    So, every time you will be opening the Access file, the function "MyImportsDrive" will be rebuilding the path of the saved imports automatically, depending on the computer that will be running, and you don't have to thinking this issue when you go to bed.

  15. #15
    hendrikbez is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    138
    Hi John

    Thank you for the info.

    I did add the code to "on load" event. So when I start it, it does give me the messagebox.
    I did add the function to a module.
    But I am not sure, I have a button to import the data (what must I use to run this butoon when clicked) or do I understand you wrong here.

    I did put this in for button, but nothing is happening, it maybe that I have 2 different imports (different data for different tables) for this access file.

    Code:
    Private Sub btnmake_sintbl_Click()
    Call MyImportsDrive
    End Sub
    Last edited by hendrikbez; 09-16-2019 at 01:45 AM.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Tricky field formats in excel imports
    By tonygg in forum Access
    Replies: 3
    Last Post: 10-13-2015, 02:23 PM
  2. Access 2010 Saved Imports
    By Tomfernandez1 in forum Access
    Replies: 1
    Last Post: 09-27-2011, 12:27 PM
  3. Need Frequent Excel Imports with Primary Keys
    By UMassEngineer in forum Import/Export Data
    Replies: 1
    Last Post: 03-27-2011, 10:07 AM
  4. Adding a 0 to single digit imports
    By TheWolfster in forum Access
    Replies: 6
    Last Post: 02-16-2010, 05:19 PM
  5. Tab Delimited Imports
    By SandyDandy in forum Import/Export Data
    Replies: 1
    Last Post: 02-20-2009, 08:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums