I have a table that contains the names of other tables in my database and
when a new table is added, i'd like to add its name to the list as part of a
VBA module. Seems a pretty basic function, but I can't find any instruction
on how to do it in the Help pages.

I've tried things like:
Recordsets!SubRateCards.Fields("RateTable") = NewName

But this just gets me an error message saying that an object is required.

(UNTESTED)

dim sql as string, v1 as string, v2 as integer
v1 = "abc" ' value for field F1.
v2 = 99 ' value for field F2.
sql = "INSERT INTO MyTable (F1, F2) VALUES (""" & v1 & """, " & v2 &
")"
msgbox sql
dbengine(0)(0).execute sql, dbfailonerror

- or -

with dbengine(0)(0).openrecordset("MyTable", dbopendynaset)
.addnew
![F1] = v1
![F2] = v2
.update
end with

HTH,
TC (MVP Access)
http://tc2.atspace.com

I would do something like this:

dim db as database
dim tdf as tabledef
dim rst as recordset
set db = currentdb()
set tdf = db.tabledefs("SubRateCard")
set rst = tdf.openrecordset(dbopendynaset)
with rst
..addnew
![RateTable] = NewName
..update
end with

Hope this helps
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


"LeoB" wrote:

> I have a table that contains the names of other tables in my database and
> when a new table is added, i'd like to add its name to the list as part of a
> VBA module. Seems a pretty basic function, but I can't find any instruction
> on how to do it in the Help pages.
>
> I've tried things like:
> Recordsets!SubRateCards.Fields("RateTable") = NewName
>
> But this just gets me an error message saying that an object is required.

Hmm. I'm getting a Type Mismatch error when it tries to open the recordset
with that. I tried with db as the object instead of tdf and got the same.

"MJatAflac" wrote:

> I would do something like this:
>
> dim db as database
> dim tdf as tabledef
> dim rst as recordset
> set db = currentdb()
> set tdf = db.tabledefs("SubRateCard")
> set rst = tdf.openrecordset(dbopendynaset)
> with rst
> .addnew
> ![RateTable] = NewName
> .update
> end with
>
> Hope this helps
> --
> Michal Joyce
> Project Management IS Analyst
> Aflac - Project Management Office
>
>
> "LeoB" wrote:
>
> > I have a table that contains the names of other tables in my database and
> > when a new table is added, i'd like to add its name to the list as part of a
> > VBA module. Seems a pretty basic function, but I can't find any instruction
> > on how to do it in the Help pages.
> >
> > I've tried things like:
> > Recordsets!SubRateCards.Fields("RateTable") = NewName
> >
> > But this just gets me an error message saying that an object is required.

Don't know for sure but it sounds like the data you are trying to insert into
the table is not of the same datatype as the field you are trying to put it
into.

In my example the name of the table is SubRateCard and the name of the
column in the table is RateTable

regards,
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


"LeoB" wrote:

> Hmm. I'm getting a Type Mismatch error when it tries to open the recordset
> with that. I tried with db as the object instead of tdf and got the same.
>
> "MJatAflac" wrote:
>
> > I would do something like this:
> >
> > dim db as database
> > dim tdf as tabledef
> > dim rst as recordset
> > set db = currentdb()
> > set tdf = db.tabledefs("SubRateCard")
> > set rst = tdf.openrecordset(dbopendynaset)
> > with rst
> > .addnew
> > ![RateTable] = NewName
> > .update
> > end with
> >
> > Hope this helps
> > --
> > Michal Joyce
> > Project Management IS Analyst
> > Aflac - Project Management Office
> >
> >
> > "LeoB" wrote:
> >
> > > I have a table that contains the names of other tables in my database and
> > > when a new table is added, i'd like to add its name to the list as part of a
> > > VBA module. Seems a pretty basic function, but I can't find any instruction
> > > on how to do it in the Help pages.
> > >
> > > I've tried things like:
> > > Recordsets!SubRateCards.Fields("RateTable") = NewName
> > >
> > > But this just gets me an error message saying that an object is required.

Thanks, Michal, but I don't think it can be that, as the thing grinds to a
halt before it gets to that point - stops at the OpenRecordset statement. I
can't see what the problem is from reading the help pages.

rgds,

Leo

"MJatAflac" wrote:

> Don't know for sure but it sounds like the data you are trying to insert into
> the table is not of the same datatype as the field you are trying to put it
> into.
>
> In my example the name of the table is SubRateCard and the name of the
> column in the table is RateTable
>
> regards,
> --
> Michal Joyce
> Project Management IS Analyst
> Aflac - Project Management Office
>
>
> "LeoB" wrote:
>
> > Hmm. I'm getting a Type Mismatch error when it tries to open the recordset
> > with that. I tried with db as the object instead of tdf and got the same.
> >
> > "MJatAflac" wrote:
> >
> > > I would do something like this:
> > >
> > > dim db as database
> > > dim tdf as tabledef
> > > dim rst as recordset
> > > set db = currentdb()
> > > set tdf = db.tabledefs("SubRateCard")
> > > set rst = tdf.openrecordset(dbopendynaset)
> > > with rst
> > > .addnew
> > > ![RateTable] = NewName
> > > .update
> > > end with
> > >
> > > Hope this helps
> > > --
> > > Michal Joyce
> > > Project Management IS Analyst
> > > Aflac - Project Management Office
> > >
> > >
> > > "LeoB" wrote:
> > >
> > > > I have a table that contains the names of other tables in my database and
> > > > when a new table is added, i'd like to add its name to the list as part of a
> > > > VBA module. Seems a pretty basic function, but I can't find any instruction
> > > > on how to do it in the Help pages.
> > > >
> > > > I've tried things like:
> > > > Recordsets!SubRateCards.Fields("RateTable") = NewName
> > > >
> > > > But this just gets me an error message saying that an object is required.

Try changing your declaration to

dim rst as DAO.recordset

I'm assuming that you've got references set to both the Microsoft DAO 3.6
Object Library and the Microsoft ActiveX Data Objects 2.1 Library, and the
ADO one is higher in the list (With any code module open, select Tools |
References from the menu bar to see what I'm talking about)

When you have both references, you'll find that you need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

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


"LeoB" <(E-Mail Removed)> wrote in message
news:6C1EA01D-D0AE-472B-8255-(E-Mail Removed)...
> Thanks, Michal, but I don't think it can be that, as the thing grinds to a
> halt before it gets to that point - stops at the OpenRecordset statement.
> I
> can't see what the problem is from reading the help pages.
>
> rgds,
>
> Leo
>
> "MJatAflac" wrote:
>
>> Don't know for sure but it sounds like the data you are trying to insert
>> into
>> the table is not of the same datatype as the field you are trying to put
>> it
>> into.
>>
>> In my example the name of the table is SubRateCard and the name of the
>> column in the table is RateTable
>>
>> regards,
>> --
>> Michal Joyce
>> Project Management IS Analyst
>> Aflac - Project Management Office
>>
>>
>> "LeoB" wrote:
>>
>> > Hmm. I'm getting a Type Mismatch error when it tries to open the
>> > recordset
>> > with that. I tried with db as the object instead of tdf and got the
>> > same.
>> >
>> > "MJatAflac" wrote:
>> >
>> > > I would do something like this:
>> > >
>> > > dim db as database
>> > > dim tdf as tabledef
>> > > dim rst as recordset
>> > > set db = currentdb()
>> > > set tdf = db.tabledefs("SubRateCard")
>> > > set rst = tdf.openrecordset(dbopendynaset)
>> > > with rst
>> > > .addnew
>> > > ![RateTable] = NewName
>> > > .update
>> > > end with
>> > >
>> > > Hope this helps
>> > > --
>> > > Michal Joyce
>> > > Project Management IS Analyst
>> > > Aflac - Project Management Office
>> > >
>> > >
>> > > "LeoB" wrote:
>> > >
>> > > > I have a table that contains the names of other tables in my
>> > > > database and
>> > > > when a new table is added, i'd like to add its name to the list as
>> > > > part of a
>> > > > VBA module. Seems a pretty basic function, but I can't find any
>> > > > instruction
>> > > > on how to do it in the Help pages.
>> > > >
>> > > > I've tried things like:
>> > > > Recordsets!SubRateCards.Fields("RateTable") = NewName
>> > > >
>> > > > But this just gets me an error message saying that an object is
>> > > > required.

Hmmm The code I sent you makes use of DAO as opposed to ADO that could be the
problem I suppose. This type of code works for me all the time doing exactly
what you are trying to do.

Check to see if you have a reference to Microsoft DAO 3.51 Object Library in
your database.

In case you don't know how to do this. Open any module and click on
Tools>References and then scroll through the list and then select the
reference you need to add.

This might solve the problem otherwise I think it will take one of the MVP's
to answer this.

My only other thought is where are you getting the new table name from?
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


"LeoB" wrote:

> Thanks, Michal, but I don't think it can be that, as the thing grinds to a
> halt before it gets to that point - stops at the OpenRecordset statement. I
> can't see what the problem is from reading the help pages.
>
> rgds,
>
> Leo
>
> "MJatAflac" wrote:
>
> > Don't know for sure but it sounds like the data you are trying to insert into
> > the table is not of the same datatype as the field you are trying to put it
> > into.
> >
> > In my example the name of the table is SubRateCard and the name of the
> > column in the table is RateTable
> >
> > regards,
> > --
> > Michal Joyce
> > Project Management IS Analyst
> > Aflac - Project Management Office
> >
> >
> > "LeoB" wrote:
> >
> > > Hmm. I'm getting a Type Mismatch error when it tries to open the recordset
> > > with that. I tried with db as the object instead of tdf and got the same.
> > >
> > > "MJatAflac" wrote:
> > >
> > > > I would do something like this:
> > > >
> > > > dim db as database
> > > > dim tdf as tabledef
> > > > dim rst as recordset
> > > > set db = currentdb()
> > > > set tdf = db.tabledefs("SubRateCard")
> > > > set rst = tdf.openrecordset(dbopendynaset)
> > > > with rst
> > > > .addnew
> > > > ![RateTable] = NewName
> > > > .update
> > > > end with
> > > >
> > > > Hope this helps
> > > > --
> > > > Michal Joyce
> > > > Project Management IS Analyst
> > > > Aflac - Project Management Office
> > > >
> > > >
> > > > "LeoB" wrote:
> > > >
> > > > > I have a table that contains the names of other tables in my database and
> > > > > when a new table is added, i'd like to add its name to the list as part of a
> > > > > VBA module. Seems a pretty basic function, but I can't find any instruction
> > > > > on how to do it in the Help pages.
> > > > >
> > > > > I've tried things like:
> > > > > Recordsets!SubRateCards.Fields("RateTable") = NewName
> > > > >
> > > > > But this just gets me an error message saying that an object is required.

They should pay you guys Douglas!
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


"Douglas J. Steele" wrote:

> Try changing your declaration to
>
> dim rst as DAO.recordset
>
> I'm assuming that you've got references set to both the Microsoft DAO 3.6
> Object Library and the Microsoft ActiveX Data Objects 2.1 Library, and the
> ADO one is higher in the list (With any code module open, select Tools |
> References from the menu bar to see what I'm talking about)
>
> When you have both references, you'll find that you need to "disambiguate"
> certain declarations, because objects with the same names exist in the 2
> models. For example, to ensure that you get a DAO recordset, you'll need to
> use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
> Dim rsCurr As ADODB.Recordset)
>
> The list of objects with the same names in the 2 models is Connection,
> Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
> and Recordset
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "LeoB" <(E-Mail Removed)> wrote in message
> news:6C1EA01D-D0AE-472B-8255-(E-Mail Removed)...
> > Thanks, Michal, but I don't think it can be that, as the thing grinds to a
> > halt before it gets to that point - stops at the OpenRecordset statement.
> > I
> > can't see what the problem is from reading the help pages.
> >
> > rgds,
> >
> > Leo
> >
> > "MJatAflac" wrote:
> >
> >> Don't know for sure but it sounds like the data you are trying to insert
> >> into
> >> the table is not of the same datatype as the field you are trying to put
> >> it
> >> into.
> >>
> >> In my example the name of the table is SubRateCard and the name of the
> >> column in the table is RateTable
> >>
> >> regards,
> >> --
> >> Michal Joyce
> >> Project Management IS Analyst
> >> Aflac - Project Management Office
> >>
> >>
> >> "LeoB" wrote:
> >>
> >> > Hmm. I'm getting a Type Mismatch error when it tries to open the
> >> > recordset
> >> > with that. I tried with db as the object instead of tdf and got the
> >> > same.
> >> >
> >> > "MJatAflac" wrote:
> >> >
> >> > > I would do something like this:
> >> > >
> >> > > dim db as database
> >> > > dim tdf as tabledef
> >> > > dim rst as recordset
> >> > > set db = currentdb()
> >> > > set tdf = db.tabledefs("SubRateCard")
> >> > > set rst = tdf.openrecordset(dbopendynaset)
> >> > > with rst
> >> > > .addnew
> >> > > ![RateTable] = NewName
> >> > > .update
> >> > > end with
> >> > >
> >> > > Hope this helps
> >> > > --
> >> > > Michal Joyce
> >> > > Project Management IS Analyst
> >> > > Aflac - Project Management Office
> >> > >
> >> > >
> >> > > "LeoB" wrote:
> >> > >
> >> > > > I have a table that contains the names of other tables in my
> >> > > > database and
> >> > > > when a new table is added, i'd like to add its name to the list as
> >> > > > part of a
> >> > > > VBA module. Seems a pretty basic function, but I can't find any
> >> > > > instruction
> >> > > > on how to do it in the Help pages.
> >> > > >
> >> > > > I've tried things like:
> >> > > > Recordsets!SubRateCards.Fields("RateTable") = NewName
> >> > > >
> >> > > > But this just gets me an error message saying that an object is
> >> > > > required.
>
>
>

Thanks, guys!

Leo

"MJatAflac" wrote:

> They should pay you guys Douglas!
> --
> Michal Joyce
> Project Management IS Analyst
> Aflac - Project Management Office
>
>
> "Douglas J. Steele" wrote:
>
> > Try changing your declaration to
> >
> > dim rst as DAO.recordset
> >
> > I'm assuming that you've got references set to both the Microsoft DAO 3.6
> > Object Library and the Microsoft ActiveX Data Objects 2.1 Library, and the
> > ADO one is higher in the list (With any code module open, select Tools |
> > References from the menu bar to see what I'm talking about)
> >
> > When you have both references, you'll find that you need to "disambiguate"
> > certain declarations, because objects with the same names exist in the 2
> > models. For example, to ensure that you get a DAO recordset, you'll need to
> > use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
> > Dim rsCurr As ADODB.Recordset)
> >
> > The list of objects with the same names in the 2 models is Connection,
> > Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
> > and Recordset
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no private e-mails, please)
> >
> >
> > "LeoB" <(E-Mail Removed)> wrote in message
> > news:6C1EA01D-D0AE-472B-8255-(E-Mail Removed)...
> > > Thanks, Michal, but I don't think it can be that, as the thing grinds to a
> > > halt before it gets to that point - stops at the OpenRecordset statement.
> > > I
> > > can't see what the problem is from reading the help pages.
> > >
> > > rgds,
> > >
> > > Leo
> > >
> > > "MJatAflac" wrote:
> > >
> > >> Don't know for sure but it sounds like the data you are trying to insert
> > >> into
> > >> the table is not of the same datatype as the field you are trying to put
> > >> it
> > >> into.
> > >>
> > >> In my example the name of the table is SubRateCard and the name of the
> > >> column in the table is RateTable
> > >>
> > >> regards,
> > >> --
> > >> Michal Joyce
> > >> Project Management IS Analyst
> > >> Aflac - Project Management Office
> > >>
> > >>
> > >> "LeoB" wrote:
> > >>
> > >> > Hmm. I'm getting a Type Mismatch error when it tries to open the
> > >> > recordset
> > >> > with that. I tried with db as the object instead of tdf and got the
> > >> > same.
> > >> >
> > >> > "MJatAflac" wrote:
> > >> >
> > >> > > I would do something like this:
> > >> > >
> > >> > > dim db as database
> > >> > > dim tdf as tabledef
> > >> > > dim rst as recordset
> > >> > > set db = currentdb()
> > >> > > set tdf = db.tabledefs("SubRateCard")
> > >> > > set rst = tdf.openrecordset(dbopendynaset)
> > >> > > with rst
> > >> > > .addnew
> > >> > > ![RateTable] = NewName
> > >> > > .update
> > >> > > end with
> > >> > >
> > >> > > Hope this helps
> > >> > > --
> > >> > > Michal Joyce
> > >> > > Project Management IS Analyst
> > >> > > Aflac - Project Management Office
> > >> > >
> > >> > >
> > >> > > "LeoB" wrote:
> > >> > >
> > >> > > > I have a table that contains the names of other tables in my
> > >> > > > database and
> > >> > > > when a new table is added, i'd like to add its name to the list as
> > >> > > > part of a
> > >> > > > VBA module. Seems a pretty basic function, but I can't find any
> > >> > > > instruction
> > >> > > > on how to do it in the Help pages.
> > >> > > >
> > >> > > > I've tried things like:
> > >> > > > Recordsets!SubRateCards.Fields("RateTable") = NewName
> > >> > > >
> > >> > > > But this just gets me an error message saying that an object is
> > >> > > > required.
> >
> >
> >