Adding Tables through Visual Basic

  • Thread starter Thread starter Nancy
  • Start date Start date
N

Nancy

I posted this and got a response on how to do this in
DAO. I don't know how to use DAO, and would rather have a
visual basic method if there is one.
Any Suggestions?
I have a database where I want take a number from a form
[Num1] and add that many records to a table [Table1].
In the form, I have a list box which lists the job
numbers. I wanted to use a for loop and add each of the
jobnumbers using the .itemdata() property.

I can't seem to figure out how to add the records.
Along with adding the job number into the new records, I
would like to add the current date.

Thanks for any help.
..
 
There's no such thing as "a visual basic method" to add tables. Your only
choices for doing it programmatically are to use DAO, use ADOX, or to run a
CREATE TABLE SQL statement, which requires using DAO or ADO to run the SQL.

That having been said, though, I don't see that you're actually trying to
add a table. It sounds more like you're trying to add data to an existing
table. And again, to do that programmatically, you're either going to have
to open a recordset (using either DAO or ADO), loop through the recordset to
get the details for each record, and add to the recordset, or else run an
APPEND query (which requires the use of DAO or ADO)

DAO and ADO are the data access models that are the only way to communicate
with a Jet database (i.e. an .MDB file) programmatically. ADOX is an
extension to the ADO model that lets you work with the metadata, rather than
the data itself.
 
There is no "strict" vb metod to add records to table.
You can use only vb in DAO or ADO context.
Use previous response.
 
losmac said:
There is no "strict" vb metod to add records to table.
You can use only vb in DAO or ADO context.
Use previous response.
Click to expand...

You can also use SQL script like "CREATE TABLE......." and execute it....
without
DAO or ADO...!

Alessandro(IT).
 
DO I have to do something to use the DAO? I use the code
builder in Access and it does not recognize the commands
that were previously given to me. DO I have to do
something first in order to use DAO?

-----Original Message-----
There's no such thing as "a visual basic method" to add tables. Your only
choices for doing it programmatically are to use DAO, use ADOX, or to run a
CREATE TABLE SQL statement, which requires using DAO or ADO to run the SQL.

That having been said, though, I don't see that you're actually trying to
add a table. It sounds more like you're trying to add data to an existing
table. And again, to do that programmatically, you're either going to have
to open a recordset (using either DAO or ADO), loop through the recordset to
get the details for each record, and add to the recordset, or else run an
APPEND query (which requires the use of DAO or ADO)

DAO and ADO are the data access models that are the only way to communicate
with a Jet database (i.e. an .MDB file) programmatically. ADOX is an
extension to the ADO model that lets you work with the metadata, rather than
the data itself.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



I posted this and got a response on how to do this in
DAO. I don't know how to use DAO, and would rather have a
visual basic method if there is one.
Any Suggestions?
I have a database where I want take a number from a form
[Num1] and add that many records to a table [Table1].
In the form, I have a list box which lists the job
numbers. I wanted to use a for loop and add each of the
jobnumbers using the .itemdata() property.

I can't seem to figure out how to add the records.
Along with adding the job number into the new records, I
would like to add the current date.

Thanks for any help.
.
Click to expand...


.
Click to expand...
 
DO I have to do something to use the DAO?
Click to expand...

There is no need to get too excited about things like DAO or ADO -- these
are just libraries of objects and methods that VBA used to create and edit
tables and records. Remember that there are only about a dozen keywords in
VBA, and all the useful stuff is provided by various libraries.

If you are working just with Access mdb files, then DAO is faster, easier
and more capable, but you have to enable it (it used to be on by default up
to ver 97) by locating it in the Tools -> References dialog in the VB
Editor (look for Microsoft Data Access Objects 3.61). If you don't want
ADO, uncheck it in the same dialog.

You do want ADO if you are going to be programming from VBScript, ASP and
so on; or if you want to use SQL Server or other ODBC databases.

The difference between them is something like

' in DAO
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDB()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)


' in ADO
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

Set con = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open strSQL, con, 0, adCmdText, adOpenStatic


As you see, they do similar jobs, and the structure is all VBA, but the
libraries provide the actual objects and methods like Open.

Hope that helps a bit



Tim F
 
I want take a number from a form
[Num1] and add that many records to a table
Click to expand...

Despite the coding help you have received, I meant to add that there is
very rarely any reason to add a set of empty or dummy records, and this
nearly always reveals a design flaw.

If you need to store which dates JobNumbers were issued on, have you
thought about a table like

Issues
------
*DateIssued StartNumber EndNumber
=========== ----------- -----------
2003-12-09 10998776 11000003
2003-12-10 11000004 11000187

On the other hand, if you actually have job details to fill in, then the
record should realistically be created at the time that there is something
to put in it: IssuedTo, Costing, TimeAllocation or whatever. This is
naturally the role of a form and becomes very easy.

Hope that helps


Tim F
 
You can also use SQL script like "CREATE TABLE......." and execute it....
without
DAO or ADO...!

Alessandro(IT).
Click to expand...

Not to quibble too much, but you will need DAO or ADO objects to
execute that. Connection, Database or QueryDef.

- Jim
 
Jim Allensworth said:
Not to quibble too much, but you will need DAO or ADO objects to
execute that. Connection, Database or QueryDef.

- Jim
Click to expand...

Docmd.RunSQL "CREATE TABLE...." nothing about ADO or DAO.
The First question whas about CREATE NEW TABLE remember....!
You Need DAO or ADO Library only to use Collection Object, but not
to CREATE any of this..........!!!
Access also is strange one because use always DAO.
Only ADP project with SQL/MSDE don't need DAO library, but using JET
if you read Recordset with ADO JET Engine convert it in DAO Recordset...!

Alessandro(IT)
 
Docmd.RunSQL "CREATE TABLE...." nothing about ADO or DAO.
The First question whas about CREATE NEW TABLE remember....!
You Need DAO or ADO Library only to use Collection Object, but not
to CREATE any of this..........!!!
Access also is strange one because use always DAO.
Only ADP project with SQL/MSDE don't need DAO library, but using JET
if you read Recordset with ADO JET Engine convert it in DAO Recordset...!

Alessandro(IT)
Click to expand...
Ah, yes. DoCmd, I had forgotten about that - since I don't use it. I
generally prefer CurrentDb.Execute for action queries. Nonetheless you
are correct - DoCmd is an Access object.

Exception withdrawn. :-)

- Jim
 
Back
Top