Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KnooKie
Aged Yak Warrior
623 Posts |
 Posted - 2002-01-22 : 09:39:29
|
| Bit of an Access question really but i thought i'd throw it in here anyway.When linking SQL Server tables into MSAccess we always seem to have a 'dbo_' prefix before every table name.is there an easy way of removing this suffix without manually going through each table one at a time ?many thanksPaul |
|
|
beatz001
Starting Member
1 Post |
Posted - 2002-01-22 : 10:06:47
|
| You can use a macro to link a series of tables. Use the command 'Transfer Database', on the Destination field fill in the Name you would prefer to call the table. Then run the macro, this is good if you find yourself always relinking to the same tables. |
 |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-01-22 : 10:11:22
|
| I haven't used access a lot recently but I always get the dbo issue as well. I generally leave it be and haven't had any problems. Not sure about beatz001's solution - I thought transfer database actually copied the data. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-22 : 12:05:57
|
| You get that because Access includes the owner of the table as well as the table name when linking remote tables. I've had this happen both with SQL Server and Oracle links. The period is not a legal character in an Access object name, so it changes it to an underscore:dbo.myTable ==> dbo_myTableYou can remove it without harm as long as the name doesn't already exist. The only automatic way of changing it is to write some VB code that uses the DAO methods to access the TableDefs in your Access database. The one problem you should be aware of is that not all tables may have dbo as the owner (most will, but if you always assume this it'll bite you in the ass later). DAO TableDefs have a SourceTableName or some such property that tells you the original owner and table name. It's not too complicated to write if you're good with VB.In fact, you could use the same methods to write your own linking wizard kinda-thing that will cut the owner out when the table is linked. Check the Access help file, and look at the DAO section closely under TableDef objects. I did something like this a while back but I doubt I'll find the code for it. I kept linked table names in an Access table and had a macro run through each and relink them according to the data in Access. If I have some time I'll try rewriting it and posting it, but give it a shot yourself and see what happens. |
|
|
|
|
|
|
Linking tables into MSAccess