473,321 Members | 1,669 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Lookup field linked to access database not refreshing

lilp32
43
I have a program that contains a lookup formula that references data linked to an Access database.

I have "ActiveWorkbook.RefreshAll" at the beginning of the program but sometimes the workbook does not refresh. Any suggestions?

Also, is there VBA code I can use to cross reference instead of putting a lookup formula in every cell?
Jun 24 '16 #1

✓ answered by zmbd

Reviewing some notes I have on this, it appears that the default "Background Refresh" setting is "True" and can result in some hang-ups. If this happens to you then
Ribbon>Data>Connections>Connections
Dialog opens
Select the connection in the dialog box
Properties command button
Usage tab
Uncheck the {Enable Background Refresh}
CHECK MARK {Refresh this connection on Refresh All}
(You might want to check that this setting is selected.. should be by default IIRC, in addition to using the ThisWorkbook.RefreashAll)

There are other options to explore here too

:)

-Z

3 1135
zmbd
5,501 Expert Mod 4TB
1) When the refresh doesn't occur, have you tried simply pressing [Shift][F9] to make sure that the data link is still working?

2) Change ActiveWorkbook.RefreshAll
to ThisWorkbook.RefreshAll

The problem with ActiveWorkbook.RefreshAll is if the workbook doesn't have the focus then the workbook you intended to have refreshed doesn't refresh the data. For example, IF you were to use VBA to instance the workbook in question (call it WrkBk-B) from another Workbook (WrkBk-A)- Then WrkBk-A actually has the focus and not WrkBk-B; of course, there are other reasons WrkBk-B wouldn't have the active state resulting in the ActiveWorkbook.RefreshAll failing to update your formulas.

If (2) doesn't fix your problem then we need a bit more information here...
1) How is the Workbook attached to the database
2) What is the formula in the cell

If this does solve the issue - please set this as best answer.
Jun 24 '16 #2
lilp32
43
Thank you - that makes sense. I will try it and see how it goes.

For the second question, I connected the access table through the "get external data" menu in Excel, so I think is uses OLE DB. The access table is located on the "Study Numbers" worksheet. I use the formula: strFormula = "=IFERROR(VLOOKUP('MRSA'!RC[2],'Study numbers'!C:C[1],2,FALSE),"""")".
Jun 28 '16 #3
zmbd
5,501 Expert Mod 4TB
Reviewing some notes I have on this, it appears that the default "Background Refresh" setting is "True" and can result in some hang-ups. If this happens to you then
Ribbon>Data>Connections>Connections
Dialog opens
Select the connection in the dialog box
Properties command button
Usage tab
Uncheck the {Enable Background Refresh}
CHECK MARK {Refresh this connection on Refresh All}
(You might want to check that this setting is selected.. should be by default IIRC, in addition to using the ThisWorkbook.RefreashAll)

There are other options to explore here too

:)

-Z
Jun 28 '16 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Smith_X | last post by:
I have access database which I willuse form to retrieve a field to show in List of combo box. the method that I use now is adodb code when form load is show below. Private Sub Form_Load() Dim...
1
by: Zachary Turner | last post by:
I want to make a Lookup Field based on another Lookup field. In other words, I have this table A with two fields: ID and Name, where ID is an Autonumber and Name is a friendly name. Then I have a...
0
by: aji | last post by:
Hello, I have question about an encrypted password field in an MSAccess database file. My task is to migrate a shopping cart from ASP/Access to PHP/MySQL... which is quite new territory for...
0
by: Sandi | last post by:
I have a simple problem: I have an Access database (images.mdb) that has 2 columns: one is the id of the picture (an integer) and one (column named picture) is a field of type OLE Object which...
2
by: charliej2001 | last post by:
Hi all Im copying details from an Outlook contact address book into an Access database. Originally I was only copying about 10 fields across, using the the following method, with a line of code...
4
by: Tony | last post by:
I am trying this: SQL = "INSERT INTO GradeList (Date) Values(#01-01-2007#)" I get message "Syntax error in Insert statement". How to add date field in access database?
1
by: Shyam Barnwal | last post by:
Hi sir, Good afternoon, I am working on winamp general purpose pluggin "gen_whatsplaying " project downloaded from the site www.schaffrath.net I compiled and build the project successfully...
1
by: Rekha Angappan | last post by:
I have to insert a date/time field in access database from my java code. But the date/time field format in DB is MM/DD/YYYY HH/MM/SS AM/PM.I tried the code below. DateTime date=new DateTime(); ...
5
by: Doicare | last post by:
Hi all I am facing a problem in storing an array of integers to an OLE object field in the database, the used code for this action is listed below: Sub Import_data() Dim oAccesss As...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.