Results 1 to 4 of 4
  1. #1
    janmack79 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    30

    ODBC Import into Excel

    I have 10 tables from my Access DB that I have linked into an Excel Report that I have created using ODBC access to my DB. The issue is that if I forget to close the Database before I hit Refresh or Refresh All in Excel I get these two error messages attached. And the only way to I seem to be able to fix the issue is to relink each table all over again and since I have already formatted the tables in Excel this is quite time consuming.

    Is there any way to prevent the error from happening if I have Access open when I go to hit refresh or a faster way to fix it if I forget to close Access with out having to link the tables all over again....
    Attached Thumbnails Attached Thumbnails Capture.PNG   Capture 1.PNG  

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you can test for the existence if your database name with a .laccdb extension, if it's there put up a msgbox as a warning and cancel the code

    i.e.
    Code:
    dim fs
    
    set fs = createobject("scripting.filesystemobject")
    
    if fs.fileexists("c:\test\mydatabasename.laccdb") then
        Msgbox "You have your database open dummy", vbokonly, "Exiting Function"
        End Sub
    else
        'do what your code is currently doing
    endif

  3. #3
    janmack79 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    30
    Ok but where exactly would I enter this code that would check for that?? On a side note I did discover that if I just restart my computer that the errors go away and I am able to Refresh after that so at least I have that in the meantime.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If you've narrowed the problem down such that you know having an open database is the problem the code I gave you should be in the ON CLICK event of your button in excel (or other detectable event). If you're not using a button to trigger the refresh and you are strictly using the menu/ribbon bar then you're out of luck. If you can't trigger code if you're using menus/ribbon bars, but you can trigger menus/ribbon bar options if you are using code.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  2. Schedule daily overnight import or refresh with ODBC connection
    By kagoodwin13 in forum Import/Export Data
    Replies: 11
    Last Post: 11-13-2015, 11:37 AM
  3. Replies: 10
    Last Post: 06-16-2014, 08:37 AM
  4. Import or link fields via ODBC
    By smoked1 in forum Import/Export Data
    Replies: 9
    Last Post: 10-30-2009, 03:55 AM
  5. Unable to import or link tables through odbc in Access SP2
    By Dave Jenkins in forum Import/Export Data
    Replies: 3
    Last Post: 11-09-2005, 11:51 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