Table not showing up in Access with ODBC connection

[weigoldk]

I'm very new to SQL and am stumbling my way through. I don't write scripts and I'm just getting started with the SQL management studio.

I have an ODBC connection set up on my PC. It tested successfully. I have the default set to my database in this case called MyDatabase. I know I have records in the database because I was able to export it and attach Access to the exported mdb.

However, my next step is to connect Access to the SQL database using the above ODBC connection. When I try, all I see are system database tables but NOT the MyDatabase table.

I'm sure I'm missing something very basic. Any help would be appreciated.

 

[SQLBill]

How did you create the MyDatabase table? It is my guess you created it without specifying dbo as the owner:

CREATE TABLE dbo.MyTable

So it was created with your schema as the owner:

Me.Mytable

Then when you connect via Access, you are probably using a different login which means a different schema. You can't see another user's tables unless:

1. you have permission to view them
2. you fully identify the table:
SELECT * FROM Me.MyTable

If you find that is the issue, you can use sp_changeobjectowner to change the table's owner to dbo.

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875