Problems in Importing Data From Sql Server

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.

All Forums

Other Forums

MS Access

Problems in Importing Data From Sql Server

Author

Topic

bkmurti
Starting Member

13 Posts

Posted - 2002-11-21 : 04:21:16

hai,

i have a peculiar problem . i imported a SQL Server database (only tables) into an Access Database.I could have exported it from SQl Server DTS wizard but the system is hanging in mid Process. I have about 158 tables with varying no of records.

what happened is when i imported the table names came as
dbo_tablename as user in database is "dbo".

I cant go on renaming each individual table . but i want it to only
"tablename" not "dbo_tablename"

is there any way

thank u
murti

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-11-21 : 05:14:40

I asked this question once.

Here are the replies i got
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=12243

this has been asked a lot on this site so try doing a forum search as well.

===========
Paul

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-21 : 09:12:09

Open or create a MODULE in Access.

Type this in:


public sub RemoveDBO()

  dim t as tabledef
  for each t in currentdb.tabledefs
    if left(t.name,4) = "dbo_" then t.name = MID(t.name,5)
  next
  msgbox "All Done!"
end sub

Then, just run this subroutine. Either put the cursor anywhere in it
and hit F5, or from the debug window, just type RemoveDBO and hit enter.

Run this every time you are done linking all of your tables.

WARNING -- there is no error checking in the above, so make sure that no tables have the names that this procedure will try to create.

- Jeff

Edited by - jsmith8858 on 11/21/2002 09:12:52

scottpt
Posting Yak Master

186 Posts

Posted - 2003-11-05 : 13:22:31

What did you have to add to get the tabledef?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-05 : 14:10:25

not sure what you are asking, but you do need a VB reference to DAO if you are using Access 2000.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources