Results 1 to 12 of 12
  1. #1
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62

    MS Access Front End, Google Cloud Platform - MySQL Back End

    Hi All,

    Thank you in advance for an assistance on this topic. I have successfully setup a virtual machine with MySQL 5.7 on GCP (Google Cloud Platform), but I am having zero luck getting an ODBC connection with MySQL ODBC Connector 3.51. Has anyone done anything similar with using GCP MySQL as a back end to their MS Access Database?

    I am able to ping the GCP MySQL public IP, so I know that my computer is seeing the server.

    Error message:



    Connection Failed: [HY000][MySQL][ODBC 3.51 Driver] Can't connect to MySQL server on 'xx.xxx.xx.xxx'(10060).

  2. #2
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    I am now able to connect to my GCP MySQL database using MySQL ODBC 8.0 Driver.

    I created a pass-through query that links to the GCP database, via odbc 8.0, and an append query in MS Access that loads the data to the pass-through query. This works -- but it is very slow.

    Does any have any advice on speeding up the connection between MS Access and GCP (via odbc 8.0)?

    Thanks!

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Is it possible or practical to provide a copy of the database in some manner so others could test and experience the issue(s) you are finding?

  4. #4
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Thanks for the reply orange. Normally I would post the db or some examples, but since I'm dealing with a linked table to GCP, I wouldn't be able to do that without providing credentials, etc.

    I know -- I'm not helping myself out much by providing limited information. I just thought someone else may have used MS Access as a front end to a (cloud-based) MySQL database and had trouble with appending data.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    connections over the internet are very slow compared with LAN's - so you need to take a lesson from web page design - transfer as little data as possible.

    Also when using ODBC you need to be aware that the sql should not use any VBA functions - it would still work but will be slow because the driver needs to return data acted on by these functions to the vba environment to be processed. Common examples might include iif, nz and date functions although the odbc driver is pretty good at converting some them to the host equivalent. Consider using stored procedures (which typically can only take a record at a time when updating/appending) called via vba looping through your recordset.

    Otherwise you are looking at the your internet connection and the service level of performance you are paying google. Make sure you are using a google server local to you - connecting to a server on the other side of the Atlantic will be significantly slower.

  6. #6
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Thanks for the feedback Ajax. I definitely understand the connectivity issue, but the data that I am sending to GCP is minuscule compared to, for example, steaming Netflix...

    There has to be a faster solution for transferring data from a local ms access db to a cloud based storage -- especially since ms access has a 2gb size limit (what a joke, btw).

    Please see the query below that is appending to a linked table (on GCP):

    Code:
    INSERT INTO tracking_log ( Client_Code, Carrier_Code, Mode, Account_Number, Bill_Type, Invoice_Number, Net_Charges, Tracking_Number, Sender_Company_Name, Sender_Name, Sender_Address_1, Sender_Address_2, Sender_City, Sender_State, Sender_Postal, Sender_Country, Receiver_Company_Name, Receiver_Name, Receiver_Address_1, Receiver_Address_2, Receiver_City, Receiver_State, Receiver_Postal, Receiver_Country, Ship_Date, Service, Tracking_Service_Code, Saturday_Delivery, Additional_Handling_Weight, Additional_Handling_Size, Additional_Handling_Longest_Side, Residential, Zone_Code, Package_Type, Ship_Weight, Bill_Weight )
    SELECT cca.client_code AS Client_Code, cca.carrier_code AS Carrier_Code, service_codes.mode AS Mode, sd1.account_number AS Account_Number, sd1.bill_option_code AS Bill_Type, sd1.invoice_number AS Invoice_Number, charges.net_charges AS Net_Charges, sd1.tracking_number AS Tracking_Number, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,cca.company_name,sd1.sender_company_name) AS Sender_Company_Name, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,'',sd1.sender_name) AS Sender_Name, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,cca.company_address_1,sd1.sender_address_line_1) AS Sender_Address_1, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,cca.company_address_2,sd1.sender_address_line_2) AS Sender_Address_2, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,cca.company_city,sd1.sender_city) AS Sender_City, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,cca.company_state,sd1.sender_state) AS Sender_State, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,cca.company_zip,sd1.sender_postal) AS Sender_Postal, IIf(sd1.bill_option_code='P/P' And sd1.sender_postal Is Null,cca.company_country,sd1.sender_country_territory) AS Sender_Country, sd1.receiver_company_name AS Receiver_Company_Name, sd1.receiver_name AS Receiver_Name, sd1.receiver_address_line_1 AS Receiver_Address_1, sd1.receiver_address_line_2 AS Receiver_Address_2, sd1.receiver_city AS Receiver_City, sd1.receiver_state AS Receiver_State, sd1.receiver_postal AS Receiver_Postal, sd1.receiver_country_territory AS Receiver_Country, Format(sd1.transaction_date,'mm/dd/yyyy') AS Ship_Date, service_codes.universal_description AS Service, service_codes.tracking_service_code AS Tracking_Service_Code, ACC_Flags.sat AS Saturday_Delivery, ACC_Flags.ahw AS Additional_Handling_Weight, ACC_Flags.ahs AS Additional_Handling_Size, ACC_Flags.ahl AS Additional_Handling_Longest_Side, ACC_Flags.res AS Residential, sd1.zone AS Zone_Code, sd1.container_type AS Package_Type, sd1.entered_weight AS Ship_Weight, sd1.billed_weight AS Bill_Weight
    FROM (((shipment_details AS sd1 LEFT JOIN service_codes ON sd1.charge_description_code = service_codes.service_code) LEFT JOIN carrier_client_accounts AS cca ON (sd1.Account_Number = cca.ACCOUNT_NUMBER) AND (sd1.Recipient_Number = cca.MASTER_ACCOUNT_NUMBER)) LEFT JOIN sat_ahw_ahs_resi AS ACC_Flags ON (sd1.Invoice_Number = ACC_Flags.invoice_number) AND (sd1.Tracking_Number = ACC_Flags.tracking_number)) LEFT JOIN (SELECT tracking_number,invoice_number, 
                              Sum(net_amount) AS Net_Charges 
                       FROM   shipment_details AS xxx 
                       WHERE  xxx.charge_classification_code NOT IN ( 'FSC' ) 
                       GROUP  BY tracking_number,invoice_number)  AS charges ON (sd1.Tracking_Number = charges.tracking_number) AND (sd1.Invoice_Number = charges.invoice_number)
    WHERE (((sd1.charge_classification_code) Not In ('FSC','INF','ACC','BRK','GOV')) AND ((sd1.charge_category_detail_code) Not In ('IBS','PSUP','HAZ','MISC','FPOD','CADJ','RADJ','SCC','OCA','OCG','DCON','CTGR','AGSR','GSR','FEES','VOID','SVCH','ADC','ADJ','DIN','ZONE','PDP','SDS')));
    Would it be possible to have a temp table as an intermediary table, i.e:

    Query Above -> temp table -> insert all records from temp table to linked table

    Also, I am not familiar with a store procedure -- would you please provide an example? Thank you

  7. #7
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Quote Originally Posted by NSearch View Post
    Would it be possible to have a temp table as an intermediary table, i.e:

    Query Above -> temp table -> insert all records from temp table to linked table
    Tried this and it didn't help -- still extremely slow.

  8. #8
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Here are some options in the MySQL ODBC Connector (v8.0)
    Click image for larger version. 

Name:	connection.png 
Views:	22 
Size:	74.4 KB 
ID:	38774

    Click image for larger version. 

Name:	metadata.png 
Views:	21 
Size:	71.9 KB 
ID:	38775

    Click image for larger version. 

Name:	cursors.png 
Views:	21 
Size:	74.4 KB 
ID:	38776

    Click image for larger version. 

Name:	misc.png 
Views:	21 
Size:	74.3 KB 
ID:	38773

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    stored procedures are written in the host BE. Haven't got mySQL running right now but in sql server you'll find it under programmability.

    for examples, plenty out there - google/bing something like 'mysql create stored procedure to append data'

    As said before, I suspect your performance issue is down to the level of connection resources you have and perhaps the volume of data. I can assure you that netflix and the like will be paying significantly more for a higher level of service. When using the web you really need to cut down on data volumes. See this link for ways of bulk loading data

    https://medium.com/@benmorel/high-sp...l-9d3dcd76f723

    Basically you copy the data to the mySQL server as a text file of similar and then all the insert action takes place on the server.

    It is a common mistake, but to be clear the 2Gb limit on Access refers to the JET or ACE database engine, not Access. Access is basically a client based front end development tool which can use a multitude of different rdbms's as a back end - as you are doing now. 2Gb is still quite substantial - I have developed apps with millions of rows of data without reaching that limit, but my next port of call would normally be sql server/express - still accessed via a LAN. The only reason for moving to the web is for reasons of accessibility from multiple locations which are not on the LAN - and even then I would look at using Citrix or Terminal Server.

  10. #10
    PBCN is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    24
    Im using Azure Sql, used this youtube series to set up the connection https://www.youtube.com/watch?v=i3yb...SPr5FZ&index=3
    Its only a small backend but it feels fast enough on the office internet, but struggles a bit sometimes when using the phone wifi

  11. #11
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Quote Originally Posted by Ajax View Post
    stored procedures are written in the host BE. Haven't got mySQL running right now but in sql server you'll find it under programmability.

    for examples, plenty out there - google/bing something like 'mysql create stored procedure to append data'

    As said before, I suspect your performance issue is down to the level of connection resources you have and perhaps the volume of data. I can assure you that netflix and the like will be paying significantly more for a higher level of service. When using the web you really need to cut down on data volumes. See this link for ways of bulk loading data

    https://medium.com/@benmorel/high-sp...l-9d3dcd76f723

    Basically you copy the data to the mySQL server as a text file of similar and then all the insert action takes place on the server.

    It is a common mistake, but to be clear the 2Gb limit on Access refers to the JET or ACE database engine, not Access. Access is basically a client based front end development tool which can use a multitude of different rdbms's as a back end - as you are doing now. 2Gb is still quite substantial - I have developed apps with millions of rows of data without reaching that limit, but my next port of call would normally be sql server/express - still accessed via a LAN. The only reason for moving to the web is for reasons of accessibility from multiple locations which are not on the LAN - and even then I would look at using Citrix or Terminal Server.

    Ajax, Thank you! I've used LOAD DATA local INFILE before, but didn't think I could use it in this situation. I had to set some flags (client and server side) to allow the data to load, but it is working.

    Now I just need to figure out how to export (using vba) the table I need as text -> store in a temp directory -> call:

    Code:
    xSQL = "LOAD DATA local INFILE 'C:/Users/XX/Documents/tracking_log_temp.txt' INTO TABLE tracking_log FIELDS TERMINATED BY ',' ENCLOSED BY '""' ESCAPED BY ''"

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Simply export the table and save the export specification then use DoCmd.TransferText acExportDelim with the specification name.

    Cheers,
    Vlad

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-28-2018, 12:07 AM
  2. Replies: 1
    Last Post: 06-09-2016, 04:03 PM
  3. Replies: 3
    Last Post: 07-30-2014, 05:04 PM
  4. Database Front Ends on a Cloud Server
    By rts in forum Database Design
    Replies: 0
    Last Post: 04-04-2012, 07:17 PM
  5. Replies: 1
    Last Post: 01-04-2012, 01:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums