[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

I can no longer import the results from an Access back end query to Excel without error

I have a back end Access database file which is also used as the data source for an Excel spreadsheet.  It has run successfully for several years until this morning, when it gives me a Run time error 1004 message when trying to refresh the excel table which is linked with a specific query.

Capture1.JPG
When attempting to create a new link to the back end file from excel, I get prompted by this box..

Capture2.JPG
...which seems to require a username and password - which isn't required to open the database - and will only work if no-one is currently using the database.

Any help gratefully received.
0
thechrisrobertstoo
Asked:
thechrisrobertstoo
  • 4
  • 3
1 Solution
 
Carol ChisholmCommented:
Is the database nearly 2GB? There is a size limit in Access.
0
 
thechrisrobertstooAuthor Commented:
No, it's around 100k.
0
 
Carol ChisholmCommented:
Anti-virus got the file open?
Change in security policy on the server?
Trusted locations in Access or Excel?
Anything else updated over the weekend?
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
thechrisrobertstooAuthor Commented:
None of the above :(
0
 
thechrisrobertstooAuthor Commented:
Hmm, I've found a solution.  I amended a field on the query last Friday and changed it to a calculation, however, because the fields I wanted to include in the calculation are sometimes null, I used the nz function.  Changing this to the iif function has fixed the issue, at least as far as the existing excel spreadsheet is concerned.  So Crisis over.

Thank you for your interest.
0
 
Carol ChisholmCommented:
well done
0
 
thechrisrobertstooAuthor Commented:
I worked out what had caused the issue, even if I'm not entirely sure why.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now