Re: Problem with a repeated select from MS Access

[Daniel Kasak]

Osvaldo Sommer wrote:
This is the code:
Public Sub Graba_Recibido(texto As String)
277    On Error GoTo Manejo_Error
278    Dim db As Database
279    Dim rec As DAO.Recordset
281    texto = Mid(texto, 2, 2) & Mid(texto, 7, Len(texto) - 7)
282    texto = Mid(texto, 1, Len(texto) - 1)
284    Set db = CurrentDb
285    Set rec = db.OpenRecordset("select * from BALANZA_RECEPCION where
1=0", dbOpenDynaset, dbSeeChanges, dbOptimistic) ' abro la tabla
286    rec.AddNew                                  ' Agrego uno nuevo
287    rec!rc_data = texto
288    rec!rc_fecha = CDbl(Date)
289    rec!rc_procesado = "N"
290    rec.Update                                  ' Actualizo
información
291    rec.Close                                   ' cierro cursor
292    Set db = Nothing
296    Exit Sub
297 Manejo_Error:
298    Set db = Nothing
299    Procesa_Error "DibalTimer - Funciones Locales - Graba_Recibido",
True
End Sub
 

If you're doing this rapidly, I would take another approach completely. 
I haven't tested this, but my 'hunch' is that adding records via a 
DAO.Recordset.AddNew method would be the slowest and most error prone 
way of doing it. My preference would be use use ADO, and have a global 
connection object, and probably a global command object, and keep 
reusing the same objects. This is for speed and stability. I'm not 100% 
sure what is causing your problem, but I'm almost certain that the below 
method will work around it.

To start with, when the Access database opens, open an ADO connection 
object and an ADO command object:

---
Sub on_database_startup
Global mysql_conn as ADODB.Connection
Global mysql_command as ADODB.Command
Set mysql_conn = New ADODB.Connection
With mysql_conn
.ConnectionString = "Driver={MySQL ODBC 3.51 
driver};Server=192.168.0.1;DATABASE=my_database_name;UID=some_user_name;PWD=some_password"
.Open
End With

Set mysql_command = New ADODB.Command
With mysql_command
.ActiveConnection = mysql_conn
.CommandType = acCommandTxt
End With
End Sub
---
Note that the connection string should be on one line.
Now that you have both the above objects loaded in memory and connected 
to MySQL, your code that inserts the data would simply be:

---
Public Sub Graba_Recibido(texto As String)
On Error GoTo Manejo_Error
texto = Mid(texto, 2, 2) & Mid(texto, 7, Len(texto) - 7)
texto = Mid(texto, 1, Len(texto) - 1)
With mysql_command
.CommandText = "insert into BALANZA_RECEPCION ( rc_data, rc_fecha, 
rc_procesado )" _
 & " values ( " '" & texto & "', '" & Format(Date, "yyyy-mm-dd") & "', 
'N' )"
.Execute
End With

End Sub
---
You could also use MySQL's curdate() function instead of using the 
client Date() function:

---
With mysql_command
.CommandText = "insert into BALANZA_RECEPCION ( rc_data, rc_fecha, 
rc_procesado )" _
 & " values ( " '" & texto & "', curdate(), 'N' )"
.Execute
End With

---
You also have to watch that you don't have text with quotes in the 
string texto - if this is possible, you'll have to escape them before 
sending the SQL to MySQL.

It's possible that you can do the above with DAO - I haven't used DAO - 
I jumped straight into ADO, and it works very well for me. The idea with 
DAO would be the same - create your objects at the start and reuse them.

I've got a small section on my website about using Access with MySQL. 
Have a look at http://entropy.homelinux.org/MySQL/ and specifically 
http://entropy.homelinux.org/MySQL/optimizing.html#ado

And this is the error report i got
-----*-----*-----*-----*-----*-----*-----*-----*-----*-----*-----*-----*
----
-*
Fecha: 02/03/2005 Hora: 11:43:22 AM
Error en DibalTimer - Funciones Locales - Graba_Recibido:
Error #3151 (Línea: 285) (Source: DAO.Database) ODBC--connection to
'{MySQL ODBC 3.51 Driver}dellserver' failed.
 

You may find out more info on what's causing your problems by checking 
the mysql logs. For example, is Access taking up the maximum number of 
connections ( opening connections and not closing them )?

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]