Hi! I'm Adelina and I need some help :) (1 Viewer)

[Adelina_RO]

So, here it goes: i've been using Access since the 97 version (yeah, I'm old...), but only recently i had the need to use a MariaDB server as the backend and I'm stomped...
I've been trying to make a pass-through query to append some rows in the said backend to noavail...
This is the situation:
I have a [tmpClient] local table in Access frontend which contains about 2K rows and i want to append the [Client] table which is located on the server but is linked in the frontend.
I can use the "Execute" statement of currentdb, but it takes too long (~1 min), so... no.
So i thought of using pass-t query to let the server do all the work, but when i use the query "INSERT INTO Client (IdClient, CNP, Nume, NumeAsociat) SELECT tmpClient.IdClient, tmpClient.CNP, tmpClient.Nume, tmpClient.NumeAsociat FROM tmpClienti" it doesn't work, because it expects the [tmpClient] to be on the server as well.
This brings me to the question at hand:
Can i use a pass-t query to append a MariaDB table with data extracted from a local (access) table?
Thank you in advance!

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!

 

[Isaac]

Welcome!

 

[CJ_London]

To answer your question, the short answer is no for the reason you stated.

you can pass the values of each record by creating a sql string in vba and executing that as a pass through on a record by record basis but suspect it won’t be any faster than using the execute method

another option is to use a stored procedure on Mariadb but again on a record by record basis, passing the field values as parameters

I’m not familiar with Mariadb but it may be possible to pass the recordset as an array

 

[theDBguy]

Hi. Welcome to AWF!

 

[pbaldy]

Welcome! FYI, I moved your thread out of the introductions forum.

 

[gemma-the-husky]

@Adelina_RO

A minute doesn't seem long if it's a one-off.
Are you doing this regularly with different data sets?

I see you have another thread running as well. Hopefully you will see this note, anyway.

 

[Adelina_RO]

Hey it's a few times only, true, but i was thinking ahead besides, I'm new to the access-mysql combo, so i wanted to learn something new. Anyway, i got it working so

 

[lcarpay]

Hi Adelina, I used SQL Server for this sample but there is no real difference in using MariaDB (except of course the specific features for each environment). The story: for performance reasons I had to take a subset of the records, resting on the server-side, to a local table and do the user GUI-magic locally. The parameters come from the user form.

Maybe it's helpful.

Private Sub RefreshListOfActivities()
  Const procName As String = "PlanAct.Form_Party_Invoice_frm.RefreshListOfActivities"
  On Error GoTo Err_Sub

  Dim qdf As QueryDef
  Dim strSql As String
 
  'lstActivitiesToInvoice
  Set qdf = CurrentDb.QueryDefs("GetPartyInvoice_LIST_ptqry")
  strSql = "exec pa.GetPartyInvoice_LIST " & _
              "@businessUnitId = " & Me!cbxBusinessUnit & ", " & _
              "@dateFrom = '" & Me!DateFrom & "', " & _
              "@dateTo = '" & Me!DateTo & "', " & _
              "@statusGroup = " & Me!cbxstatusGroup & ", " & _
              "@statusProjectActivity = " & Me!cbxStatusProjectActivity & ", " & _
              "@firstResponsibleId = " & Me!cbxFirstResponsible & ", " & _
              "@projectId = " & Me!cbxProject
  qdf.Sql = strSql
  qdf.Close
  Set qdf = Nothing
 
  Call RunSql("delete * from _GetPartyInvoice_LIST;")
  strSql = "insert into _GetPartyInvoice_LIST (ActivityId, ProjectId, PartyId, InvoiceContactId, ActivityCustomerStatusId, VatId, Klant, Referentie, Contact, Datum, Opdracht, Project, Bedrag, Regels, GroepsNr) " & _
                                       "select ActivityId, ProjectId, PartyId, InvoiceContactId, ActivityCustomerStatusId, VatId, Klant, Referentie, Contact, Datum, Opdracht, Project, Bedrag, Regels, GroepsNr from GetPartyInvoice_LIST_ptqry;"

  Call RunSql(strSql)
  Me!lstActivitiesToInvoice.RowSource = "_GetPartyInvoice_LIST"

Exit_Sub:
  On Error GoTo 0
  Exit Sub

Err_Sub:
  Call LogError(Err.Number, Err.Description, procName, , True)
  Resume Exit_Sub
End Sub