Can not find SQL table

  • Thread starter Thread starter Tod
  • Start date Start date
T

Tod

Please excuse my newbieness.

I don't know which NG is best for this question, but I'll
start here.

I'm connecting to a SQL database to get data for an Excel
workbook. I have some tables that need to join to one
that is in the SQL database. Since I'm not allowed to
create tables on the SQL database, I did this.

I created an Access database, created an ODBC link to the
SQL table, created some of my own tables in the Access
database, and linked them the way I need them. Then I
wrote a query in Access to extract the data I need. Then
I wrote some ADO code in the Excel workbook to connect to
the Access database and run the query and return the data.

It worked fine.

However, I now get the following error when the ADO code
tries to run the Access query:

[Microsoft][ODBC Microsoft Access Driver] The Microsoft
Jet database engine cannot find the input table or
query 'dbo_tablename'. Make sure it exists and that its
name is spelled correctly.

Notice it doesn't say anything about the query, only the
table that the query includes. Now if I open Access and
run the query it works fine.

Could someone give me some pointers on where I might be
going wrong?

tod
 
In SQL Server, dbo.Tablename is valid syntax, but in Access, it has to
translate it to dbo_Tablename.

So, it can't find a tablename with the dbo_ prefix, thus it bombs.

I'm not sure why you needed Access to connect Excel to SQL, as I think you
can go straight there. (See this month's Access Advisor for an article on
SQL reports via Excel.)
 
Back
Top