Hi,
Was wondering if anybody could help.

I'm looking to add a lots of records to an access database, but it looks
like I'm going to have to insert them one at a time!!!
Looks like I'm going to have to do this for each new record..

Insert into Database(<fields>) VALUES(<values>)

Was wondering if there was anything similar to the MySQL Implementation
where I can add several new rows in one SQL command.

Many thanks

Wayne

You could use: DataAdapter.FillSchema() to create a mapped DataTable that
looks like the original, but has no data in it. You could then populate
that table with your new data and then just use: DataAdapter.Update().


"Wayne Gibson" <(E-Mail Removed)> wrote in message
news:dm1sjm$prs$1$(E-Mail Removed)...
> Hi,
> Was wondering if anybody could help.
>
> I'm looking to add a lots of records to an access database, but it looks
> like I'm going to have to insert them one at a time!!!
> Looks like I'm going to have to do this for each new record..
>
> Insert into Database(<fields>) VALUES(<values>)
>
> Was wondering if there was anything similar to the MySQL Implementation
> where I can add several new rows in one SQL command.
>
> Many thanks
>
> Wayne
>

Wayne,

You can use Scotts solution, however be aware, they will forever inserted
one in a time.

A dataadapter uses the same commands as you do.

Just a little addition.

Cor

"Wayne Gibson" <(E-Mail Removed)> schreef in bericht
news:dm1sjm$prs$1$(E-Mail Removed)...
> Hi,
> Was wondering if anybody could help.
>
> I'm looking to add a lots of records to an access database, but it looks
> like I'm going to have to insert them one at a time!!!
> Looks like I'm going to have to do this for each new record..
>
> Insert into Database(<fields>) VALUES(<values>)
>
> Was wondering if there was anything similar to the MySQL Implementation
> where I can add several new rows in one SQL command.
>
> Many thanks
>
> Wayne
>

Thanks for replying..

But surely this is just doing multi insert intos?

The MySQL version of insert into works like this...

INSERT INTO table (col1, col2, col3)
VALUES
('val1','val2','val3'),
('val1','val2','val3'),
('val1','val2','val3');

I know that when I converted from executing individual insert into to this
insert into on a MySQL it was a great improvement..

The problem is that I have about 55000 inserts to do and I wanted a quick
way to do it..
Any other ideas?

Thanks

Wayne

Wayne,

I think that you are after this,

Bulkinsert.
http://msdn.microsoft.com/library/de...ba-bz_4fec.asp

However this is SQLserver 2005, I don't believe it exist on Access.

In this page are links to deeper explanation of Access and SQL
http://msdn.microsoft.com/library/de.../acfundsql.asp

I hope this helps,

Cor

"Wayne Gibson" <(E-Mail Removed)> wrote in message
news:dm1sjm$prs$1$(E-Mail Removed)...
> Hi,
> Was wondering if anybody could help.
>
> I'm looking to add a lots of records to an access database, but it looks
> like I'm going to have to insert them one at a time!!!
> Looks like I'm going to have to do this for each new record..
>
> Insert into Database(<fields>) VALUES(<values>)
>
> Was wondering if there was anything similar to the MySQL Implementation
> where I can add several new rows in one SQL command.
>

You still need to do it one record a time, but you could use an Access
Stored Proc as shown in the article at
http://www.devcity.net/Articles/18/msaccess_sp.aspx

The only real advantage would be in not having to send the <fields> list
over each time.

Cor Ligthert [MVP] wrote:
> Wayne,
>
> I think that you are after this,
>
> Bulkinsert.
> http://msdn.microsoft.com/library/de...ba-bz_4fec.asp

Be careful when using it, because you have to specify that any
constraints on table_name are checked during the bulk copy operation. By
default, constraints are ignored.
>
> However this is SQLserver 2005, I don't believe it exist on Access.
>
> In this page are links to deeper explanation of Access and SQL
> http://msdn.microsoft.com/library/de.../acfundsql.asp
>
> I hope this helps,
>
> Cor
>
>