Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    HarryRam is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Location
    India
    Posts
    15

    Insert data from Local MSAccess table to MYSQL table

    MSAccess version:- 2010


    MS Access local table name:- Ticket_Master_CR
    MySql Table name:- Ticket Master

    Hellow,

    I have a form created which stores the data to my MS Access local table named 'Ticket_Master_CR'. Then the data needs to be moved to MySQL table named 'Ticket Master'
    Below command created for the above transaction.

    I created a table named Ticket_Master_CR in MYSQL table & Execute the same in MySQL workbench, wherein the transaction works perfectly & the data got appended without any issues.
    So, the test worked fine.

    Now remove that table from MYSQL server & restored in my MS Access application, wherein while executing the command, receives error msg as table name Ticket_Master_CR is not available in SQL server.

    sqlstr = "INSERT INTO `Ticket Master`(`EDI Ref`,`Booking Office`,`Date Received`,`Time Received`,`Processed by`,`Date Completed`,`Time Completed`,`Booking Number`,`Date Started`,`Time Started`,`Request Status`,Query_Type2,Query_MSG2,`Query_Start-2`,`Query_End-2`,Query_Type3,Query_MSG3,`Query_Start-3`,`Query_End-3`,Query_Type4,Query_MSG4,`Query_Start-4`,`Query_End-4`,Query_Type5,Query_MSG5,`Query_Start-5`,`Query_End-5`,Request_Source,Amendment_Type,ReStart_1,ReStart _2,ReStart_3,ReStart_4) SELECT `EDI Ref`,`Booking Office`,`Date Received`,`Time Received`,`Processed by`,`Date Completed`,`Time Completed`,`Booking Number`,`Date Started`,`Time Started`,`Request Status`,Query_Type2,Query_MSG2,`Query_Start-2`,`Query_End-2`,Query_Type3,Query_MSG3,`Query_Start-3`,`Query_End-3`,Query_Type4,Query_MSG4,`Query_Start-4`,`Query_End-4`,Query_Type5,Query_MSG5,`Query_Start-5`,`Query_End-5`,Request_Source,Amendment_Type,ReStart_1,ReStart _2,ReStart_3,ReStart_4 FROM `Ticket Master_CR`;"

    Click image for larger version. 

Name:	Datasaveerror.JPG 
Views:	34 
Size:	22.7 KB 
ID:	41419

    So, need your help as how to handle this situation.

    Any suggestions as how the command to be created as how to define the local table name

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Access requires object names with spaces or special characters to be delimited with [ ] not apostrophes. Apostrophes are used to delimit text parameters. Try replacing all those apostrophes with [ ] pairs.

    Why are you entering to local table and copying to MySQL instead of just entering to linked table?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    HarryRam is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2015
    Location
    India
    Posts
    15
    Quote Originally Posted by June7 View Post
    Access requires object names with spaces or special characters to be delimited with [ ] not apostrophes. Apostrophes are used to delimit text parameters. Try replacing all those apostrophes with [ ] pairs.

    Why are you entering to local table and copying to MySQL instead of just entering to linked table?
    Hi June7,

    Had tried the same as well, wherein received error as MySQL statement is not correct. So cannot execute the function

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You are running this query in Access VBA?

    Assume you set a link to backend table.

    Sorry, I don't use MySQL but from what I've read, apostrophes are not appropriate for object names and [] should work. http://www.heritage-tech.net/908/ins...cel-using-vba/.

    Better would be to not use spaces and special characters in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    HarryRam is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2015
    Location
    India
    Posts
    15
    Yes, using the Access VBA & the table in MySQL table is already linked with ODBC connection.
    All I just need help is how to write the VBA code to define that the table mentioned in 'Select' statement should look for MS Access table.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If removing apostrophes and using [] does not work, I have no idea. [] are needed if names include space or special characters (underscore is only exception).

    Data structure appears to be non-normalized.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    HarryRam is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2015
    Location
    India
    Posts
    15
    Thanks June7 for your effort. Will wait for msg from any other members in this forum.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    for benefit of other responders see this link for further background - https://www.accessforums.net/showthr...390#post451390

    I'm really not sure about your use of single quotes, I'm with June in that respect, but you say it works, so be it - it seems to understand enough to not generate an error message about it!

    With regards your select part - I'm happy to be proved wrong, but the way I've always done it is with dao and using a linked table (i.e. using the linked table manager, not ado). The reason is, as your error message implies, the tables are in different rdbms's. If you have a linked table, they can both be seen in access.

    With ADO you have to get your dao or ado recordset and loop through the recordset creating INSERT queries for each row using the construct.

    INSERT INTO tablename (field1, field2,...) VALUES (value1, value2,...)

    You should also investigate using a stored procedure in mySQL and passing field values to that procedure - that also requires looping through your access recordset but will probably be faster since all the processing is on the server side.

    There are other ways, such as exporting a file (think it is xml format) copying that to the mySQL server and then importing - I don't know the detail, consult a mySQL forum for the details

  9. #9
    HarryRam is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2015
    Location
    India
    Posts
    15
    Hi Ajax,

    Noted your comments. If I need to use DAO instead of ADO, then can you please help me out as how the codes needs to be used for DAO procedure?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Set up links to both tables so you see the linked tables in Navigation Pane. Then run action SQL.

    MySQL workbench might not have issue with those apostrophes but Access SQL engine will. Actually those are 'smart apostrophes' - note that they are tilted - Word creates this character. Even if they were normal apostrophes, Access would complain.



    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by HarryRam View Post
    I have a form created which stores the data to my MS Access local table named 'Ticket_Master_CR'.
    The SQL appears to have errors:

    Comparing what you typed and the SQL you posted, there is not an underscore after "Ticket" (in red) and before "Master" - you have a space.
    Code:
    ReStart_4 FROM `Ticket Master_CR`;"

    Also, there is a space but no delimiters (2 places in the string)
    Code:
    Request_Source,Amendment_Type,ReStart_1,ReStart  _2,ReStart_3,ReStart_4) SELECT
    Code:
    ReStart_1, ReStart  _2, ReStart_3, ReStart_4

    There are mismatched parenthesiss'
    You have an open parenthesis after SELECT, but no closing parenthesis at the end
    Code:
    FROM `Ticket Master_CR`;"

    I tried to find out the value of the "apostrophes' you use in your code, but the error "Invalid character" kept being returned.
    You might also check to make sure you are using apostrophes.


    As a test, you might try this code I modified:
    Code:
    sqlstr = "INSERT INTO [Ticket Master]([EDI Ref],[Booking Office],[Date Received],[Time Received],[Processed by],[Date Completed],[Time Completed],[Booking Number],[Date Started],[Time Started],[Request Status],Query_Type2,Query_MSG2,[Query_Start-2],[Query_End-2],Query_Type3,Query_MSG3,[Query_Start-3],[Query_End-3],Query_Type4,Query_MSG4,[Query_Start-4],[Query_End-4],Query_Type5,Query_MSG5,[Query_Start-5],[Query_End-5],Request_Source,Amendment_Type,ReStart_1,ReStart_2,ReStart_3,ReStart_4) SELECT [EDI Ref],[Booking Office],[Date Received],[Time Received],[Processed by],[Date Completed],[Time Completed],[Booking Number],[Date Started],[Time Started],[Request Status],Query_Type2,Query_MSG2,[Query_Start-2],[Query_End-2],Query_Type3,Query_MSG3,[Query_Start-3],[Query_End-3],Query_Type4,Query_MSG4,[Query_Start-4],[Query_End-4],Query_Type5,Query_MSG5,[Query_Start-5],[Query_End-5],Request_Source,Amendment_Type,ReStart_1,ReStart_2,ReStart_3,ReStart_4 FROM [Ticket Master_CR]);"

  12. #12
    HarryRam is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2015
    Location
    India
    Posts
    15
    Hi Steve,Thanks for your above findings. Referring to underscore in Ticket_Master_CR, had tried correcting the same. Also, space between 'Restart' and '_2' was also corrected and executed by SQL command, but still the error indicates as unable to find this table in MYSQL database. But the table is actually in my MSACCESS FRONT END.How I can define in SQL to look for this table in the Front end I/o backend MYSQL server?

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The first thing I would do is remove all spaces from object names (fields, tables, forms, queries, reports and modules).

    Trouble shooting questions:

    Is the table in MySQL linked (ODBC) to the Access FE? You can see the linked table in Access?
    If YES, can you double click in the linked table (in the Access FE) and look at the data in the MySQL table?


    Or if you create a saved query in Access, can you see/edit the data in MySQL? (from the Access FE)
    Code:
    SELECT [EDI Ref],[Booking Office],[Date Received],[Time Received],[Processed by],[Date Completed],[Time Completed],[Booking Number],[Date Started],[Time Started],[Request Status],Query_Type2,Query_MSG2,[Query_Start-2],[Query_End-2],Query_Type3,Query_MSG3,[Query_Start-3],[Query_End-3],Query_Type4,Query_MSG4,[Query_Start-4],[Query_End-4],Query_Type5,Query_MSG5,[Query_Start-5],[Query_End-5],Request_Source,Amendment_Type,ReStart_1,ReStart_2,ReStart_3,ReStart_4 FROM [Ticket_Master_CR]);"

  14. #14
    HarryRam is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2015
    Location
    India
    Posts
    15
    Hi,

    I tried with your below revised codes in VBA, wherein received below error indicating that the code is incorrect as per MySQL query format.


    As a test, you might try this code I modified:
    Code:
    sqlstr = "INSERT INTO [Ticket Master]([EDI Ref],[Booking Office],[Date Received],[Time Received],[Processed by],[Date Completed],[Time Completed],[Booking Number],[Date Started],[Time Started],[Request Status],Query_Type2,Query_MSG2,[Query_Start-2],[Query_End-2],Query_Type3,Query_MSG3,[Query_Start-3],[Query_End-3],Query_Type4,Query_MSG4,[Query_Start-4],[Query_End-4],Query_Type5,Query_MSG5,[Query_Start-5],[Query_End-5],Request_Source,Amendment_Type,ReStart_1,ReStart_2,ReStart_3,ReStart_4) SELECT [EDI Ref],[Booking Office],[Date Received],[Time Received],[Processed by],[Date Completed],[Time Completed],[Booking Number],[Date Started],[Time Started],[Request Status],Query_Type2,Query_MSG2,[Query_Start-2],[Query_End-2],Query_Type3,Query_MSG3,[Query_Start-3],[Query_End-3],Query_Type4,Query_MSG4,[Query_Start-4],[Query_End-4],Query_Type5,Query_MSG5,[Query_Start-5],[Query_End-5],Request_Source,Amendment_Type,ReStart_1,ReStart_2,ReStart_3,ReStart_4 FROM [Ticket Master_CR]);"
    [/QUOTE]


    Click image for larger version. 

Name:	AccessNewError.JPG 
Views:	14 
Size:	33.9 KB 
ID:	41434

  15. #15
    HarryRam is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2015
    Location
    India
    Posts
    15
    Hi,

    Please find my comments after <HR_31Mar>

    Quote Originally Posted by ssanfu View Post
    The first thing I would do is remove all spaces from object names (fields, tables, forms, queries, reports and modules).

    <HR_31Mar> I would agree with you, but with the MySQL option to use that apostrophes to consider this space issue in names, then there would not be any issues. Besides as explained earlier, my entire command is working properly with the VBA code created, wherein identified the Linked tables & columns, however the concern is only as how will i define thru code in VBA to look for local msaccess tables rather than MySQL backend table.

    Trouble shooting questions:

    Is the table in MySQL linked (ODBC) to the Access FE? You can see the linked table in Access?
    <HR_31Mar> yes, i can see them. Also, this Ticket Master table is linked with other forms in MsACCESS wherein it is able to open the records & do the editing & store the data.
    If YES, can you double click in the linked table (in the Access FE) and look at the data in the MySQL table?
    <HR_31Mar> Yes can do the same as well.

    Or if you create a saved query in Access, can you see/edit the data in MySQL? (from the Access FE)
    Code:
    SELECT [EDI Ref],[Booking Office],[Date Received],[Time Received],[Processed by],[Date Completed],[Time Completed],[Booking Number],[Date Started],[Time Started],[Request Status],Query_Type2,Query_MSG2,[Query_Start-2],[Query_End-2],Query_Type3,Query_MSG3,[Query_Start-3],[Query_End-3],Query_Type4,Query_MSG4,[Query_Start-4],[Query_End-4],Query_Type5,Query_MSG5,[Query_Start-5],[Query_End-5],Request_Source,Amendment_Type,ReStart_1,ReStart_2,ReStart_3,ReStart_4 FROM [Ticket_Master_CR]);"
    <HR_31Mar>Tried this code as well, wherein system getting error msg as syntax error

    Click image for larger version. 

Name:	AccessNewError.JPG 
Views:	14 
Size:	33.9 KB 
ID:	41435

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-07-2018, 07:08 AM
  2. How to write data into linked MySQL table
    By mr.viskers in forum Programming
    Replies: 5
    Last Post: 03-13-2015, 02:51 AM
  3. Inserting Data to Access Table (Local) from MySQL table (Net)
    By gambit1430 in forum Import/Export Data
    Replies: 2
    Last Post: 09-26-2014, 01:34 AM
  4. Replies: 0
    Last Post: 05-21-2012, 11:54 PM
  5. Creating a table with local and external data
    By Accessnoooob in forum Access
    Replies: 8
    Last Post: 06-25-2011, 07:36 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