Understanding Linked Tables

[billymac]

I am having a hard time getting a thourough explanation of the theory of Linked tables to understand exactly what is going on.






Heres the details:

• Existing Access 2007 application (accdb Front end ) with split database (back End). It's a completed app that has been working great
• We needed to start using SQL server for the DB for several reasons, one being that the data needs to be used on our web site.
• We used SSMA (SQL Server Migration Assistant) to convert to SQL and link the tables . 0 errors
• Now comes the questions and errors
• We now have tables in access and SSMA that have been named SSMA$TABLENAME$local and tables with the original table names
• If I change data in SQL, the change is reflected back in access (or SSMA wizard) in the tables with the original name
• If I change data in SQL, the change is NOT reflected back in access (or SSMA wizard) in the tables with the SSMA@ name
• Is that the way it is supposed to work?

Heres how I understand Linked tables. Please correct me if I am wrong and please point me to any web site you know of with a good explanation.

1. We converted the Access DB to SQL
2. At that point we could think of this like a new access app connecting to that db
3. Access is now talking to the sql database.
What that means is that it thinks it is talking to an access db but ODBC translates everything to SQL and passes it on the SQL DB
Access APP <=> Access DB <=> [ODBC] <=> SQL Server

Is that correct?
In other words (for example) if Access has a query its running that uses an IIF statement (which SQL doesnt understand), ODBC will convert the sql to something that SQL Server understands, pass it on the SQL, SQL runs the query, and sends it back.

Now that the conversion is done and we run the access app, the first time the app tries to get data, and the ADO code runs, we get errors thrown regarding the recordset code.
Sorry I don't have the exact error or snippet of code that is running but I will get it and update this.

So, I am hoping at this point we have a code issue only and not a code issue and a Database issue.

[pbaldy]

I haven't used SSMA so I won't address those issues. Regarding this:

In other words (for example) if Access has a query its running that uses an IIF statement (which SQL doesnt understand), ODBC will convert the sql to something that SQL Server understands, pass it on the SQL, SQL runs the query, and sends it back.

No, not really. A string like "SELECT Blah FROM TableName WHERE Whatever" JET/ACE will pass the SQL to SQL Server (SS) which will process it and send back only the results. Because of the IIf(), which as you point out SS doesn't understand, SS will have to send the whole table across the wire, which JET/ACE will then process.

Off to lunch!