I have just imported 50 tables from SQL to Access and they all have the dbo
name in front of the table name

dbo_tblCustomers

I would like to know if there is VBA code to go through the table and drop
the "dbo_ " that appears before each tables name.


Thank you

Try something along the lines of
Dim tbl As DAO.tabledef

For Each tbl In TableDefs
If Left$(tbl.Name, 4) = "dbo_" Then
tbl.Name = Mid$(tbl.Name, 5)
End If
Next

Hope This Helps
Gerald Stanley MCSD
>-----Original Message-----
>I have just imported 50 tables from SQL to Access and they
all have the dbo
>name in front of the table name
>
>dbo_tblCustomers
>
>I would like to know if there is VBA code to go through
the table and drop
>the "dbo_ " that appears before each tables name.
>
>
>Thank you
>
>
>.
>

I thought the Name property was read-only. I had to try
this to verify it.....


I always used the DoCmd.Rename method to rename objects...

I did find that QueryDef.Name is also read/write, but
dbs.Containers("Forms").Documents("Form1").Name is Read
Only....

I guess you learn something every day.


Chris Nebinger


>-----Original Message-----
>Try something along the lines of
>Dim tbl As DAO.tabledef
>
>For Each tbl In TableDefs
> If Left$(tbl.Name, 4) = "dbo_" Then
> tbl.Name = Mid$(tbl.Name, 5)
> End If
>Next
>
>Hope This Helps
>Gerald Stanley MCSD
>>-----Original Message-----
>>I have just imported 50 tables from SQL to Access and
they
>all have the dbo
>>name in front of the table name
>>
>>dbo_tblCustomers
>>
>>I would like to know if there is VBA code to go through
>the table and drop
>>the "dbo_ " that appears before each tables name.
>>
>>
>>Thank you
>>
>>
>>.
>>
>.
>

I get a runtime error 424 Object required

Dim tbl As TableDef
Dim TD As TableDefs

For Each tbl In TD ****(Error - code stops Here)*****
If Left$(tbl.Name, 4) = "dbo_" Then
tbl.Name = Mid$(tbl.Name, 5)
End If
Next

"Gerald Stanley" <(E-Mail Removed)-> wrote in message
news:1c52a01c45223$fdfa2a60$(E-Mail Removed)...
> Try something along the lines of
> Dim tbl As DAO.tabledef
>
> For Each tbl In TableDefs
> If Left$(tbl.Name, 4) = "dbo_" Then
> tbl.Name = Mid$(tbl.Name, 5)
> End If
> Next
>
> Hope This Helps
> Gerald Stanley MCSD
> >-----Original Message-----
> >I have just imported 50 tables from SQL to Access and they
> all have the dbo
> >name in front of the table name
> >
> >dbo_tblCustomers
> >
> >I would like to know if there is VBA code to go through
> the table and drop
> >the "dbo_ " that appears before each tables name.
> >
> >
> >Thank you
> >
> >
> >.
> >

Try

For Each tbl In CurrentDb.TD

Hope This Helps
Gerald Stanley MCSD
>-----Original Message-----
>I get a runtime error 424 Object required
>
>Dim tbl As TableDef
>Dim TD As TableDefs
>
>For Each tbl In TD ****(Error - code stops Here)*****
> If Left$(tbl.Name, 4) = "dbo_" Then
> tbl.Name = Mid$(tbl.Name, 5)
> End If
>Next
>
>"Gerald Stanley" <(E-Mail Removed)-> wrote
in message
>news:1c52a01c45223$fdfa2a60$(E-Mail Removed)...
>> Try something along the lines of
>> Dim tbl As DAO.tabledef
>>
>> For Each tbl In TableDefs
>> If Left$(tbl.Name, 4) = "dbo_" Then
>> tbl.Name = Mid$(tbl.Name, 5)
>> End If
>> Next
>>
>> Hope This Helps
>> Gerald Stanley MCSD
>> >-----Original Message-----
>> >I have just imported 50 tables from SQL to Access and they
>> all have the dbo
>> >name in front of the table name
>> >
>> >dbo_tblCustomers
>> >
>> >I would like to know if there is VBA code to go through
>> the table and drop
>> >the "dbo_ " that appears before each tables name.
>> >
>> >
>> >Thank you
>> >
>> >
>> >.
>> >
>
>
>.
>

Actually, you don't need the TD object, but you want to instantiate a
reference to the Database:

Dim db As DAO.Database
Dim tbl As DAO.TableDef

Set db = CurrentDb()
For Each tbl In db.TableDefs
If Left$(tbl.Name, 4) = "dbo_" Then
tbl.Name = Mid$(tbl.Name, 5)
End If
Next tbl


For Each tbl In CurrentDb().TableDefs

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Gerald Stanley" <(E-Mail Removed)-> wrote in message
news:1c1f901c45251$9fb64e10$(E-Mail Removed)...
> Try
>
> For Each tbl In CurrentDb.TD
>
> Hope This Helps
> Gerald Stanley MCSD
> >-----Original Message-----
> >I get a runtime error 424 Object required
> >
> >Dim tbl As TableDef
> >Dim TD As TableDefs
> >
> >For Each tbl In TD ****(Error - code stops Here)*****
> > If Left$(tbl.Name, 4) = "dbo_" Then
> > tbl.Name = Mid$(tbl.Name, 5)
> > End If
> >Next
> >
> >"Gerald Stanley" <(E-Mail Removed)-> wrote
> in message
> >news:1c52a01c45223$fdfa2a60$(E-Mail Removed)...
> >> Try something along the lines of
> >> Dim tbl As DAO.tabledef
> >>
> >> For Each tbl In TableDefs
> >> If Left$(tbl.Name, 4) = "dbo_" Then
> >> tbl.Name = Mid$(tbl.Name, 5)
> >> End If
> >> Next
> >>
> >> Hope This Helps
> >> Gerald Stanley MCSD
> >> >-----Original Message-----
> >> >I have just imported 50 tables from SQL to Access and they
> >> all have the dbo
> >> >name in front of the table name
> >> >
> >> >dbo_tblCustomers
> >> >
> >> >I would like to know if there is VBA code to go through
> >> the table and drop
> >> >the "dbo_ " that appears before each tables name.
> >> >
> >> >
> >> >Thank you
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Your runtime error is because your code doesn't set the object variables to
anything.

Need:
Set TD=CurrentDb.TableDefs
Then the For Each loop should be able to return the tabledefs.

Paul Johnson

"LisaB" <lbagley(ReTHis)@mayatech.com> wrote in message
news:u$(E-Mail Removed)...
> I get a runtime error 424 Object required
>
> Dim tbl As TableDef
> Dim TD As TableDefs
>
> For Each tbl In TD ****(Error - code stops Here)*****
> If Left$(tbl.Name, 4) = "dbo_" Then
> tbl.Name = Mid$(tbl.Name, 5)
> End If
> Next
>
> "Gerald Stanley" <(E-Mail Removed)-> wrote in message
> news:1c52a01c45223$fdfa2a60$(E-Mail Removed)...
> > Try something along the lines of
> > Dim tbl As DAO.tabledef
> >
> > For Each tbl In TableDefs
> > If Left$(tbl.Name, 4) = "dbo_" Then
> > tbl.Name = Mid$(tbl.Name, 5)
> > End If
> > Next
> >
> > Hope This Helps
> > Gerald Stanley MCSD
> > >-----Original Message-----
> > >I have just imported 50 tables from SQL to Access and they
> > all have the dbo
> > >name in front of the table name
> > >
> > >dbo_tblCustomers
> > >
> > >I would like to know if there is VBA code to go through
> > the table and drop
> > >the "dbo_ " that appears before each tables name.
> > >
> > >
> > >Thank you
> > >
> > >
> > >.
> > >
>
>