Linked table remote update

[Trey Walpole]

Is it possible to update a linked table (linked to a SQL Server
database) in an mdb through an OLEDB or ODBC connection into that mdb?

 

[Guest]

The simple answer is yes. You will need to have rights to do that onthe SQL
side.

 

[Trey Walpole]

I'm sorry, I wasn't clear - I don't want to update the linked table from
Access to the SQL side.
I want to update the linked table from the SQL side to Access, but I
want to do it while connected to the Access database through OLEDB, not
while within Access itself.
The SQL database is for a third-party tool, from which we can pull
information into this linked table but not have a direct connection.

 

[Guest]

Trey, if you are using a linked table, it should require no update activity
to show the current data. If you are looking to put the third party data
into your own SQL tables, DTS is probably the way to go as that can be
scheduled to gather data based on your requirements. From there your Access
database can utilize the data whenever needed.

If I'm still not getting at the heart of your question, let me know.

Best regards,

John

 

[Trey Walpole]

Thanks for taking the time - I think I've been writing too quick...

We use a third-party app which has a SQL database. This is the source of
the Access linked tables. The linked table type is "Table: Linked ODBC".

I'm taking this 3rd party info and synching it up (one-way: subscribe)
with an in-house application. The only way we can pull this data from
the 3rd party SQL database is through this Access linked table, which
was provided to us and we can't change. Our in-house app connects to the
Access database through OLEDB.

Currently, the only way I've found to refresh the data is to open Access
and use the linked table manager. Instead of this, I'd like my app to be
able to do this, but while connected to the Access database through
OLEDB. How can my app do it?

 

[Guest]

Trey,

I'll take it that you are not able to just link directly to your vendor
provided Access database. Either way, unless they are storing data in the
Access DB any request that you make against it should pull directly from the
primary source as long as everything is linked. Is this something resembling
your configurations?
__________ ________________
______________
| | | |
| |
| SQL DB |----ODBC--> |Vendor AccessDB |---OLEDB--> |Your AccessDB |
|_________| |_______________|
|_____________|

If so, I take it you are taking data from tables or queries that you see in
the vendor Access DB which should always be the same as the data in their SQL
DB.

If we're still misconnecting, please let me know.

Best regards,

John