Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Importing SQL tables through ODBC

Status
Not open for further replies.

camidon

Programmer
May 9, 2000
268
0
0
US
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.
 
The code below imports a table and renames the table where highlighted with ***** this should fix your naming problem<br>&nbsp;<br>Private Sub DownloadCup3_Click()<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim DB As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim RS As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim TD As TableDef<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim IX As Index<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim FL As Field<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Stat &quot;Transfering Cup3&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;On Error Resume Next<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.DeleteObject acTable, &quot;Cup3&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;On Error GoTo 0<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.TransferDatabase acImport, &quot;ODBC Database&quot;, _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;ODBC;DSN=PRD-RTRDAVDTA&quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;;UID=winfied;PWD=wonder69;&quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;****&nbsp;&nbsp;&nbsp;&quot;DATABASE=CUP3&quot;, acTable, &quot;CUP3&quot;, &quot;CUP3&quot;****<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Set DB = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;Set TD = DB.TableDefs(&quot;CUP3&quot;)<br>&nbsp;&nbsp;&nbsp;&nbsp;Set IX = TD.CreateIndex(&quot;CUNBR&quot;)<br>&nbsp;&nbsp;&nbsp;&nbsp;Set FL = IX.CreateField(&quot;CUNBR&quot;, dbText)<br>&nbsp;&nbsp;&nbsp;&nbsp;IX.Fields.Append FL<br>&nbsp;&nbsp;&nbsp;&nbsp;TD.Indexes.Append IX<br>&nbsp;&nbsp;&nbsp;&nbsp;DB.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;Stat &quot;Done!&quot;<br>End Sub
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top