insert multiples records

J

Javcal

I have a recordset with 1500 records (Not a Table) and I want to insert it
into a a table, but if I use docmd or excecute it late more then 30 secs. I
want to do this:

Insert into Tabla ( field1, field2, field3, field4) values
(value11,value12,value13,value14) , (value21,value22,value23,value24) ,
(value31,value32,value33,value34)

I can do it on MySql, but not in acces. How can I do this with access?
 
D

Douglas J. Steele

You have to use 3 separate commands in Access:

Insert into Tabla ( field1, field2, field3, field4)
values (value11,value12,value13,value14)

Insert into Tabla ( field1, field2, field3, field4)
values (value21,value22,value23,value24) ,

Insert into Tabla ( field1, field2, field3, field4)
values (value31,value32,value33,value34)
 
D

Douglas J. Steele

Given there's no alternative, I don't see what else you can do.

How is the recordset populated in the first place?
 
J

Javcal

I give you my function

Public Sub Bajar(Tabla As String, Optional Filtro As String)
Dim MyDB As Database
Dim MyQ As QueryDef
Dim rec As DAO.Recordset
Dim Campos As String
Dim Valores As String
Dim Valor As String
Dim sql As String
Dim i As Integer

Set MyDB = CurrentDb()
Set MyQ = MyDB.CreateQueryDef("")
MyQ.Connect = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};DATABASE=
;DESCRIPTION= ;DSN= ;OPTION= ;PWD= ;PORT= ;SERVER= ;UID= "
MyQ.ReturnsRecords = False
MyQ.sql = "select * from " & Tabla & IIf(Not IsNull(Filtro), " where " &
Filtro, "")
MyQ.Execute
MyQ.ReturnsRecords = True

Set rec = MyQ.OpenRecordset
For i = 0 To rec.Fields.Count - 1
Campos = Campos & " ," & rec.Fields(i).Name
Next
Campos = " ( " & Right(Campos, Len(Campos) - 2) & " ) "
Valores = ""
Do Until rec.EOF
Valor = ""
For i = 0 To rec.Fields.Count - 1
Valor = Valor & " ,'" & rec.Fields(i).Value & "'"
Next
Valores = Valores & " , (" & Right(Valor, Len(Valor) - 2) & " ) "
If Len(Valores) > 65000 Then
Valores = Right(Valores, Len(Valores) - 2)
sql = "Insert into " & Tabla & Campos & " values " &
Right(Valores, Len(Valores) - 1)
DoCmd.RunSQL sql <<========== HERE IS MY PROBLEM *****
Valores = ""
End If
rec.MoveNext
Loop
If Len(Valores) > 1 Then
sql = "Insert into " & Tabla & Campos & " values " & Right(Valores,
Len(Valores) - 2)
DoCmd.RunSQL sql
End If
MyQ.Close
MyDB.Close
End Sub



**** In this moment I have te table in my local PC but when I whant to
insert recordset into my local table its become very slow (5 minuts for 1500
records) but if I do it in the back way I up to server 1500 recrod in 1
seconds
 
D

Douglas J. Steele

I fail to see why you're using a recordset at all.

Create a linked table, and write an INSERT INTO query. Heck, you don't even
really need to create a linked table first: you should be able to use the IN
clause in your query.

A second choice, if you really want to create the recordset based on the
MySQL table, would be to open a recordset based on Tabla, and then just add
the rows to it using the AddNew method.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top