Hello:
MSAccess 2000 sql server backend.

I have an append query(in vba) that is appending to a sql server
table.
There are 3 tables in the query, one of them is an access table.
When I run the query I get the Invalid Object Message (referencing the
access table).
Any ideas on what I can do to program vba to recognize the access
table?

below is a scaled down version of my code:

Function updatebinarytbl()
Dim strSql As String
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "DSN=SSDB", "SA", "sa"

strSql = "INSERT INTO sqlTBL1 (sqlfld1, sqlfld2, sqlfld3) " & _
"SELECT sqlTBL2.fld1, sqlTBL3.fld1, sqlTBL3.fld2 " & _
"FROM (sqlTBL2 JOIN fld ON sqlTBL2.fld = sqlTBL3.fld) INNER JOIN
accessTBL ON sqlTBL2.fld = accessTBL.fld " & _


cn.Execute strSql
cn.Close
Set cn = Nothing

End Function

Tony_E wrote:
Oohh! Bad, bad bad!!!

Never use the sa account for your application code. The sa account is
intended for system administration and is authorized to do many destructive
things that are not limited to the sql server itself. Create a
limited-rights sql account that can't do things like dropping
tables/databases or formatting hard drives and use that in your code
instead.

Hopefully that really is not the password of your sa account. If it is,
change it now!! Protect that sa password as if your job depended on it ...
it likely does!

This query is executing entirely in SQL Server. The analogous situation is
opening SSMS or EM (depending on the version of sql) and attempting to
execute the above statement in Query Analyzer or in an SSMS query window.
You would not expect to be able to run a statement that refers to an object
outside of sql server in that situation would you?

You have several options depending on the relative sizes of the sql and
access tables.
If the access table is small in comparison to the sql table, you could
import the access table into sql server before running this query (see the
TransferDatabase method). You could also create a linked server in sql
server that points at the Access table, and reference the linked server in
your query.

If instead sqlTBL2 is small in comparison to accessTBL, then you could test
the effect on performance of using linked tables in Access instead of
VBA/ADO, or at least, importing sqlTBL2 into Access and inserting the result
of the join into a linked table pointing at sqlTBL1.

On Apr 27, 8:26*am, Tony_E <bluestealt... (AT) hotmail (DOT) com> wrote:
Hi
Unfortunately I have limited access to resources. Those the are the
configurations I was to told to use when accessing the database.
Althought I know how to use Analyzer and Enterprise very well, I don't
have them here. I will try to get this to succeed using a query object
with linked tables (odbc).

Thanks very much for your input.

Tony

Tony_E wrote:
No you don't You have the sa password. You have unlimited access. You can
do anything on that sql server.

Why is that relevant? I only brought up those client tools to illustrate a
point I was making.

Should I assume you are using SQL 2000?
You don't need EM or QA for any of my recommentations. Everything I
suggested can be implemented by running some T-SQL statements using a
passthrough query.

Then you have a dba who is putting your company's data at risk. Does he
realize you've revealed the sa password to the world?

Again, that's one approach. Hopefully it will perform well for you.

On Apr 27, 10:16*am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
Again, thanks for your input. The part about revealing the password to
the world, that is my fault totally. I wasn't thinking when I created
this post.