Access 07 Export table to odbc database

Question

I have a table that I'm exporting to an odbc database in Acess 07.  This is done by right cicking the table and selecting 'export to odbc database'.  It then prompts you to select a data source.  I didn't have one, so I built one in the data source window, using SQL.  This is becuase i really wanted to export the table to a SQL database.  It succeeded, but I don't know where the database actually lives in the file system.  I can't locate it in SQL management studio.  Does anyone know where the database goes with this process?

---

Bits of Fury

Answer 1

If you just created a sql server connection and did not specify ANY particular database, then there good likely chance your table was sent to the master database. Fire up your sql studio, and the use the tree on the left side. Expand open Database node.

Then choose system databases.

Then expand the master database. 

Then expand the tables node.

You should then see your table.

If you exported using access and the sql driver, and just hit next + next + next for every default, then the above is where your table landed.

If you look close, when you choose the DSN during the ODBC connect setup, you likely saw:

If you look close, you can see the option to change the default database in the above. It defauls to master

Albert D. Kallal

Edmonton, Alberta Canada

Answer 2

That would happen if your table does not have a primary key defined. That, of course, can happen in more than one way.

First, the original table might not have had a primary key.

Second, when you linked to the table from Access, you might have been presented with a dialog box asking you to identify the primary key. If you did not do so, then Access won't know about any primary key, and can't update the table.

If the former, go to SSMS and define a primery key for the table, then go back to Access and relink the table so the existence of the primary key is refreshed.

If the latter (and there is a primary key on the table in SQL Server), delete the linked table and relink, making sure you identify that primary key during the linking.

If neither of those two possibilities are the problem, post back with additional details.

George

---

Some say Google is evil, Some Say MS is evil. "Darned" if I Google, "Darned" if I Bing.

Answer 3

under what conditions would this uploaded table become read only? I've done the same thing but one table can no longer be updated from my Access database.

Answer 4

Thank you so much, that is what happened!

---

Bits of Fury