Linked Split table file from QB ODBC not updating

[Tony Girgenti]

Hello.

Access 2002 MDB with customer table linked to Split database(_be.MDB). The
_be.MDB database has the imported customer table from an ODBC connection
through QuickBooks 5 (QODBC).

When i first set everthing up in the _be.MDB database, then link to the
_be.MDB from the .MDB database, all of the customers appear in the customer
table.

If i go to Quickbooks and add a new customer, neither the _be.MDB nor the
..MDB have the newly added customer in the customer table.

From the front end (.MDB) database, how do i programmatically update the
customer table to include the latest version of QB customers?

Any help would be gratefully appreciated.

Thanks,
Tony

 

[Col]

Hi Tony,

One method is

Dim DB As DATABASE
Dim tdf As TableDef
Set DB = CurrentDb

For Each tdf In DB.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.RefreshLink
End If
Next

Regards
Col

 

[Tony Girgenti]

Hello Col.

Thanks for your reply.

I stepped through your routine and watched it refresh all of the tables, but
it did not help. It still does not show newly added customers.

I tried exiting and restarting QuickBooks and my Access .MDB with no luck.

Thanks,
Tony

 

[Col]

Hi Tony,

Have you refreshed the tables with the Linked Table Manager?

I am not familiar with QuickBooks. Does it update its tables immediately or
do you have to post changes at some stage?

Regards
Col

 

[John Nurick]

Hi Tony,

When you import the data from Quickbooks to an Access table you are
making an independent copy of it that has no continuing link to
Quickbooks. If you want the data Access sees to be the current data in
Quickbooks, use ODBC to connect your Access front end directly to the
Quickbooks data store.

 

[Tony Girgenti]

Hello Col.

Quickbooks updates it files immediately. AFAIK, there is no posting of
changes like you do with transactions. When you add a customer, it's there.

Thanks,
Tony

 

[Tony Girgenti]

Hello John.

With the current setup i have, i run an update query in my Access MDB from
the Quickbooks linked table in _be.MDB to a another linked table which was
imported into the _be.MDB. The table i have in the .MDB called "Customers"
is updating the other table i have in the same .MDB called "QB Customers".

I don't expect to have a real time view of the Quickbooks customers from the
Access MDB program.

Thanks,
Tony

 

[John Nurick]

Hi Tony,

If I understand you correctly, you need to set up a linked table in your
front end with an ODBC connection to QuickBooks, and use this to update
your back end table.

An update query isn't sufficient, because that will only update existing
records, not add new ones. For that you need an append query too - or
you may be able to do it with a single query using the technique
described by Doug Steele in his November, 2003 "Access Answers" column
in Pinnacle Publication's "Smart Access". You can download the article
and a sample database from
http://www.accessmvp.com/DJSteele/SmartAccess.html

Hello John.

With the current setup i have, i run an update query in my Access MDB from
the Quickbooks linked table in _be.MDB to a another linked table which was
imported into the _be.MDB. The table i have in the .MDB called "Customers"
is updating the other table i have in the same .MDB called "QB Customers".

I don't expect to have a real time view of the Quickbooks customers from the
Access MDB program.

Thanks,
Tony

 

[Tony Girgenti]

Hello John.

I don't think i explained that to you correctly. However, i will check the
article you pointed to.

I want to try and explain this in the terms that you use. In my front end,
i have a link to the back end for "QB Customers" and "Customers". The back
end has a link to another MDB for "QB Customers". The back end also has an
ODBC link to the Quickbooks customer file "Customers".

In the front end, i have a purge query to delete all customers in the "QB
Customers". I also have an update query to copy all customers from the
"Customers" table to the "QB Customers" table.
front end with an ODBC connection to QuickBooks, and use this to update your
back end table. <<

If i do this, then why would i need a backend at all. Why not link all of
my tables from just the front end, as you call it.

Thanks,
Tony

Hi Tony,

If I understand you correctly, you need to set up a linked table in your
front end with an ODBC connection to QuickBooks, and use this to update
your back end table.

An update query isn't sufficient, because that will only update existing
records, not add new ones. For that you need an append query too - or
you may be able to do it with a single query using the technique
described by Doug Steele in his November, 2003 "Access Answers" column
in Pinnacle Publication's "Smart Access". You can download the article
and a sample database from
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[John Nurick]

Tony,

Now I'm getting confused. When one's talking Access and MDB files, "back
end" normally means a MDB file that contains the tables in which the
data is stored. "Front end" means a MDB file that contains the user
interface and the stuff that makes it work: forms reports and queries
and reports and modules - but not the data, which is stored in the back
end and accessed via linked tables in the front end. In normal
operations, the back end MDB is not opened: all access is via the linked
tables in the front end.

But from what you say your "back end" MDB contains linked tables that
connect to a third MDB and via ODBC to QuickBooks. But you can't have a
linked table in one MDB that is connected to a linked table in an other
MDB, so I suspect that for some of your operations you are opening what
you call the "back end" directly.

What I'm saying is that if you want to update a table in your back end
MDB to mirror the data in Quickbooks, a good way to do it is to have one
linked table *in your front end* linked via ODBC to Quickbooks and
another linked to the table in your back end.

Then you can either use a delete query on the table linked to your back
end to get rid of the existing data, and an append query (not an update
query) to copy the data from the Quickbooks-linked table to the table
linked to the back end. Or you can perhaps use Doug Steele's
single-query technique.

Hello John.

I don't think i explained that to you correctly. However, i will check the
article you pointed to.

I want to try and explain this in the terms that you use. In my front end,
i have a link to the back end for "QB Customers" and "Customers". The back
end has a link to another MDB for "QB Customers". The back end also has an
ODBC link to the Quickbooks customer file "Customers".

In the front end, i have a purge query to delete all customers in the "QB
Customers". I also have an update query to copy all customers from the
"Customers" table to the "QB Customers" table.
front end with an ODBC connection to QuickBooks, and use this to update your
back end table. <<

If i do this, then why would i need a backend at all. Why not link all of
my tables from just the front end, as you call it.

Thanks,
Tony