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

    [MICROSOFt][ODBC DRIVER MANAGER] Data source name not found and no default driver specified

    Hi,

    Windows 10 - 64 bit
    MS Access 10 - 32 bit
    ODBC connection - 32 bit

    I had an MS Access database as a front end & MYSQL as the backend. Had created the connection successfully.
    I have a form, which records a particular transaction. However, the transfer of the data from local table & then move the same to Linked table from MySQL server.

    \\\Quote\\\
    Sub uploadSQL()




    Dim conn As ADODB.Connection
    Dim sqlstr As String
    Dim RS As ADODB.Recordset
    Dim Crow As Long, Lrow As Long
    Dim Item As String, Price As Long, weight As Long, category As String


    Set conn = New ADODB.Connection


    conn.ConnectionString = "DRIVER={MySQL ODBC 8 ANSI Driver};" _
    & "SERVER=10.91.10.60;" _
    & "DATABASE=xxxxxxx;" _
    & "UID=xxxxx;PWD=xxxxxx;"


    conn.Open


    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`;"




    conn.Execute sqlstr


    conn.Close


    End Sub
    \\\Unquote\\\

    Whenever while executing the query, receiving below msg.

    Click image for larger version. 

Name:	msaccesserror.jpg 
Views:	22 
Size:	127.5 KB 
ID:	41367

    Click image for larger version. 

Name:	msaccess error.PNG 
Views:	21 
Size:	22.9 KB 
ID:	41368

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    plenty wrong with your sql

    your table is called ticket master - so you surround it with square brackets, not quotes. The same goes for the field names, you use square brackets when you have spaces. The accepted advice is to not have spaces in table and field names as they can cause problems. If you must see a space of sorts, use an underscore (_)

    Also your quotes look wrong - I see ` whereas they should be ' (chr(34)

    you don't have a space before SELECT (you should)

    and you should not surround your selection with brackets, you only do this if you are creating a INSERT by VALUE query

    See this link for more information about how queries should be constructed
    https://www.w3schools.com/sql/

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

    Thanks for your response. However, had tried the above query with MySQL Workbench & it worked to append the data without any issues.
    So, the SQL query is not the concern.

    The problem I'm facing is the system indicating unable to find the Data Source. Can you help me with how this issue can be fixed?

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Have you checked The Connection Strings Reference

    There is a page for MySQL

  5. #5
    HarryRam is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Location
    India
    Posts
    15

    Thumbs up Resolved

    Quote Originally Posted by ssanfu View Post
    Have you checked The Connection Strings Reference

    There is a page for MySQL

    Hi All,

    After reviewing the details understood that there was a mistake in the Driver details provided in the above command.

    Current command:-
    conn.ConnectionString = "DRIVER={MySQL ODBC 8 ANSI Driver};"

    Changed to:-
    conn.ConnectionString = "DRIVER={MySQL ODBC 8.0 ANSI Driver};"

    After changing the details, the connection were able to open.

    Thank you all for your support.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy you figured it out......


    Good luck with your project....

    (ready to mark this solved?)

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

Similar Threads

  1. How to install Microsoft Access ODBC driver
    By johnseito in forum Access
    Replies: 1
    Last Post: 09-18-2018, 06:27 AM
  2. Path to ODBC Driver
    By cbouley in forum Access
    Replies: 3
    Last Post: 04-23-2013, 10:00 AM
  3. ODBC Data Source driver for Visual Foxpro
    By smytelka in forum Access
    Replies: 11
    Last Post: 01-02-2013, 09:03 PM
  4. Oracle ODBC 11g Driver
    By GregZip in forum Access
    Replies: 6
    Last Post: 03-21-2012, 03:49 PM
  5. Replies: 3
    Last Post: 10-11-2010, 10:23 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