Importing SQL tables through ODBC

[camidon]

Whenever I import my 200+ tables into Access it adds "dbo_" to the front of the name of EVERY table.  Is there any way to force Access to not add this to the front of every table?  If not, then how can I set up a script to rename all of the tables that have the "dbo_" in front of them to remove the "dbo_"?  This has gotten me stumped and as I'm new to both Access and SQL a little frustrated.  Thanx in advance for the help.

 

[DougP]

DoCmd.Rename &quot;Old Employees Table&quot;, acTable, &quot;Employees&quot;<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[DougP]

Also Importing 200 + tables into one .MDB is scary to me at best.<br>In a multi user environment you are leaving yourself open for disaster. Access '97 & 2000 is notorious for corruption.<br>Leaving the .MDB completely useless.<br><br>I would put those 200 tables in a Backend .MDB<br>And make a .MDE out of the front end.<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[camidon]

All of the tables are in a backend and there is an mde front end.&nbsp;&nbsp;This does make it a little better.<br><br>How would I tell the script to automatically go in and rename all of the tables that began with

 

[JimHorton]

Frosty,<br>Just run a loop that goes like:<br><br>For i = 0 to currentdb.tabledefs.count -1<br>&nbsp;&nbsp;&nbsp;&nbsp;tn = currentdb.tabledefs(i).name<br>&nbsp;&nbsp;&nbsp;&nbsp;if left$(tn,4) = &quot;dbo_&quot; then 'Also, if the tables come from different libs/owners, you can use a lookup table or list for this string<br>&nbsp;&nbsp;&nbsp;&nbsp;tab]tn = mid$(tn,5,len(tn)-4)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;currentdb.tabledefs(i).name = tn<br>&nbsp;&nbsp;&nbsp;&nbsp;end if<br>next i<br><br>I use this commonly when linking tables in Access.&nbsp;&nbsp;What Dougp said about 2000 holds true, it is extremely unstable, but I've used '97 to prototype db's having over 500 tables (1000 when their counterparts were linked for moving data)&nbsp;&nbsp;and have had no instances at all of corruption, I think you're safe with 97.<br>--Jim

 

[camidon]

When I attempt to compile this code, I get an error that says &quot;Only comments may appear after End Sub, End Function, or End Property.&quot;&nbsp;&nbsp;Any idea as to why I'm getting this.&nbsp;&nbsp;Again, I'm a little new to Access, so your help is appreciated.&nbsp;&nbsp;<br><br>Chris