SQL Server Linked Table Name

[accessemis]

Hey all! I'm new to the forum and looking forward to learning more about Access!

My company uses our own software to visualize data that is stored in Microsoft Access databases. We also have a SQL Server but unfortunately at the moment our software is not capable of linking to SQL. I have tried linking a table in an Access database to our SQL Server and then using our software to connect to the linked table but I am running into issues...

The software we use looks for a table named tData in whichever Microsoft Access database we are using. I have successfully uploaded tData to SQL and then linked that SQL table to an Access Database table. I put new records in the SQL table to verify that the Access linked tData table can update records and it does. The linked table is properly named tData and looks the same as the original local tData table. I renamed the local one so our software should now point to the linked table. When I go to connect in our software I get the error in the image attached here. Based on the error it seems like our software doesn't see the tData table initially - it then tries to make a tData copy but tData already exists. At least that is my interpretation.

My question: does a linked table have a different format or naming convention than a local table when a 3rd party software is seeing it? I figured our software would see "tData" and use that table but I guess not!

Thanks for you help in advance!

[June7]

I am not surprised. Even Access does not see table links in another Access db. Can only link to tables, not to links.

[davegri]

My experience is that if a linked table is renamed in the FE, the server doesn't care. It's still the same table, original name there (you can't change a BE table name from the FE, right)? To do what you want, I think you need to unlink the table from the server before you rename it, then link back the actual table from the server. You say that you had a local table named the same. I don't think that's possible, but it certainly confuses the issue.

[isladogs]

If you want to link to a linked table in another database, you need to 'import' it. It will then appear as a linked table

[June7]

I set a link using External Data wizard. Then from another db I tried to link to that linked table - wizard doesn't see it. Importing the link is just another way of setting up a link to the table. Not sure this helps the OP because they can't link to SQL.

[isladogs]

When the External Data wizard is used;
a) linking to a database only lists the local tables in that database
b) importing tables lists both local and linked tables. Any linked tables that are selected also become linked tables in the new database

@accessemis
In post #1, you are trying to run a make table query (SELECT * INTO tData . . .) which in your case appears to be failing because the table already exists.
You could first delete the table (DELETE * FROM tData) then run the make table query
However I would instead recommend you just use an append query to add records to the existing table (INSERT INTO tData ... )

[accessemis]

Thanks for the response. Are you saying that I would basically set up a query to append new data from the linked table to a local table in the same Access database? I could see that working. Is there a way to automatically have that query run in the background or on database open?

[isladogs]

You can certainly append data from a linked to a local table if there is a good reason BUT you would need to ensure you don't keep importing the same records repeatedly
However, if your table is linked, there should be no need to do so. Just use the linked table data rather than copying it

[June7]

Colin, OP has stated their in-house app cannot link to SQLServer. Even though can link to Access, they cannot use a link to SQLServer established in Access db.

Qualifier for my earlier statement: Access wizard does not see links when choosing Link option, only for Import. Then it would import the Link object, not table the link references.

Yes, download can be automated but how current is current enough? Every day, hour, minute?

[isladogs]

Colin, OP has stated their in-house app cannot link to SQLServer. Even though can link to Access, they cannot use a link to SQLServer established in Access db.

No - in post #1, the OP wrote:

I have successfully uploaded tData to SQL and then linked that SQL table to an Access Database table. I put new records in the SQL table to verify that the Access linked tData table can update records and it does.

The problem is in what they then tried to do with the linked data

[June7]

Colin, maybe we really are on same page.

OP also said in post 1:
"My company uses our own software to visualize data that is stored in Microsoft Access databases. We also have a SQL Server but unfortunately at the moment our software is not capable of linking to SQL."

OP wants to make use of SQLServer at this time but unfortunately that doesn't seem practical. Entering data to SQLServer via Access then pulling that same data back down to Access local table sounds like formula for disaster. Might as well just continue with Access and migrate data to SQLServer when capabilities change.

[NoellaG]

If your software can link to an access table, but not to the SQL server table it is probably a security problem. Add a login for your app to the SQL server, add a user to the database and give this user the correct rights and probably you will be able to link to the table directly.