runtime error on vb odbc connection function

I'm trying to make a dynamic odbc connection from my access  program to a mysql database on my web site.

When I execute the function I get a runtime error -2147467259 (80004005)
[Microsoft][ODBC Driver Manager] Data source name too long

 Here's what I've pieced together so far :

Public Function PostOpenSRsToWebsite()
    Dim oConn As ADODB.Connection
    Set oConn = New ADODB.Connection
    oConn.Open "DSN=odbc2mysql, UID=nnnn, PWD=nnnn3!, DATABASE=xxxx"
    'DoCmd.SetWarnings False
    DoCmd.RunSQL ("DELETE * FROM tblServiceDisplay")
    On Error GoTo ErrMsg
    DoCmd.OpenQuery "wbqryOpenSRs2", , acAdd
    'DoCmd.SetWarnings True
    oConn.Close
    Exit Function
ErrMsg:
    MsgBox "Connection to CARE website has been lost!  Inform CARE Administrator."
     oConn.Close
    DoCmd.SetWarnings True
End Function

I'm really new at this and would appreciate any help.

jar
JudithARyanTech SupportAsked:
Who is Participating?
 
Michael VasilevskySolutions ArchitectCommented:
No message box at all? Are you sure that code is firing? rs2 isn't empty is it?
0
 
Michael VasilevskySolutions ArchitectCommented:
Try: "DSN=odbc2mysql; UID=nnnn; PWD=nnnn3!; DATABASE=xxxx;"
0
 
Michael VasilevskySolutions ArchitectCommented:
Or you may want to try a DSN-less connection:
Sub MySQLConnect()
    Dim ws As Workspace
    Dim db As Database
    Dim rs As New ADODB.Recordset
    Dim LConnect As String, strSQL As String
    Dim strDSN As String, strUID As String, strPWD As String, strServer As String
    
    strUID = "MyUID"
    strPWD = "MyPWD"
    strServer = "MyServer"

    On Error GoTo Err_Execute
    
    'Use {Microsoft ODBC for MySQL} ODBC connection
    LConnect = "Driver={Microsoft ODBC for MySQL};Server=" & strServer & ";Uid=" & strUID & ";Pwd=" & strPWD & ";"

    'Point to the current workspace
    Set ws = DBEngine.Workspaces(0)

    'Connect to remote database
    Set db = ws.OpenDatabase("", False, True, LConnect)
    
    'Select data
    rs.Open "SELECT * FROM MyTable", LConnect, adOpenStatic, adLockReadOnly
    'do what you need to do
    rs.Close
    
    db.Close

Exit_Execute:
    Exit Sub

Err_Execute:
    MsgBox Err.Description
    Resume Exit_Execute

End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
JudithARyanTech SupportAuthor Commented:
Thanks!  I had no success with your first suggestion, so I tried the second.  It seemed to do the Open, however when I did the select from table, my check for record count didn't show more than 0 records.  

rs.Open "SELECT * FROM tblServiceDisplay", LConnect, adOpenStatic, adLockReadOnly
    If rs.RecordCount > 0 Then
        MsgBox ("Records read = " & rs!RecordCount)
    End If

Am I missing something?

Thanks so-o-o much for your help.

jar
0
 
Michael VasilevskySolutions ArchitectCommented:
So your code is returning nothing? What if you try:

rs.Open "SELECT * FROM tblServiceDisplay", LConnect, adOpenStatic, adLockReadOnly
rs.MoveFirst
msgbox rs(1)

Do you see data from your table in the message box?
0
 
JudithARyanTech SupportAuthor Commented:
Yes! I got the contents of one field.  I'm unfamiliar with rs(1).  Is that referencing the 1st record or the 1st field in the 1st record.

What I need to do once the connection is established, is delete the contents of the table and then run an append query to refresh the contents of the table and disconnect.  I feel like I'm making progress for the first time!

jar
0
 
Michael VasilevskySolutions ArchitectCommented:
Great, yeah with this setup I typically move the data I need from the remote database to a temp table in the local database and go from there.
Something like:
Dim strSQL as string

strSQL = DELETE * FROM MyTempTable
CurrentDb.Execute strSQL

rs.Open "SELECT * FROM tblServiceDisplay", LConnect, adOpenStatic, adLockReadOnly
rs.MoveFirst
Do Until rs.EOF
        strSQL = "INSERT INTO MyTempTable (Field1, Field2, Field3 " & _
                       "VALUES (" & rs!Field1& ", " & rs!Field2 & ", " & rs!Field3 & ")"
        CurrentDb.Execute strSQL
        rs.MoveNext
Loop
 rs.Close
 

Open in new window


rs(1) should refer to the first field of your table. You can use it if you don't know the name of the columns. But it might actually be rs.field(1) come to think of it...

Hope this helps!

MV
0
 
JudithARyanTech SupportAuthor Commented:
MV, below is my latest try.  My access sequence is the reverse of yours.  I need to clear the mySQL table, read a local query and repopulate the mySQL table.  Processing was successful thru the Delete and Select.  Then I got an "ODBC--call failed." error message, I'm supposing on the rs.open statement.  

I'm so close!  Is there a problem with my INSERT statement?
jar


Public Function MySQLConnect()

    Dim ws As Workspace
    Dim db, db2 As Database
    Dim rs As New ADODB.Recordset
    Dim rs2 As Recordset
    Dim RecCnt As Integer
    Dim LConnect As String, strSQL As String
    Dim strDSN As String, strUID As String, strPWD As String, strServer As String
   
    strUID = "vvvv"
    strPWD = "nnnnnn"
    strServer = "sssssss"
    RecCnt = 0

    On Error GoTo Err_Execute
   
    'Use {Microsoft ODBC for MySQL} ODBC connection
    LConnect = "Driver={MySQL ODBC 5.1 Driver};Server=" & strServer & ";Uid=" & strUID & ";Pwd=" & strPWD & ";Database=" & strUID & ";"

    'Point to the current workspace
    Set ws = DBEngine.Workspaces(0)
    Set db2 = CurrentDb

    'Connect to remote database
    Set db = ws.OpenDatabase("", False, True, LConnect)
   
           
    'Delete records from web sr table
    rs.Open "DELETE FROM tblServiceDisplay", LConnect
    MsgBox ("Records deleted from web SR table.")
   
    'Get list of current srs
    Set rs2 = db.OpenRecordset("SELECT * FROM wbqryOpenSRs2")
    rs2.MoveFirst
    Do Until rs2.EOF
        'Repopulate web SR table with Open SRs
        rs.Open "INSERT INTO tblServiceDisplay (ID, ServiceType) VALUES (" & rs2!ID & ", " & rs2!ServiceType & ")""", LConnect
       
        RecCnt = RecCnt + 1
    rs2.MoveNext
    Loop
   
    MsgBox (RecCnt & " Records inserted into web SR table.")
     
    'do what you need to do
    rs.Close
    rs2.Close
    db.Close
    db2.Close
   
Exit_Execute:
    Exit Function

Err_Execute:
    MsgBox Err.Description
    Resume Exit_Execute

End Function
0
 
Michael VasilevskySolutions ArchitectCommented:
I issue is probably that you're trying to execute a SQL command on a remote database with "rs.open." "rs.open" is for opening a recordset, not executing an SQL command.
Try replacing rs.open with rs.execute
0
 
JudithARyanTech SupportAuthor Commented:
I got a compile error using rs.execute, "unknown command or data member".  The DELETE SQL command done with a rs.open prior to the SELECT was successful and deleted the records in the web table.  

jar
0
 
Michael VasilevskySolutions ArchitectCommented:
Is rs2!ServiceType a string data type? If so, the SQL needs to be (added single quote after comma and before close parensis):

rs.Open "INSERT INTO tblServiceDisplay (ID, ServiceType) VALUES (" & rs2!ID & ", '" & rs2!ServiceType & "')""", LConnect
0
 
JudithARyanTech SupportAuthor Commented:
Thanks, I just realized that maybe the problem is with the SELECT statement, so I inserted a MsgBox just after the SELECT statement and sure enough, it did not print.  So apparently I'm not distinguishing between ODBC and local SQL statements correctly.

Any ideas?

jar
0
 
JudithARyanTech SupportAuthor Commented:
No, I ran the SELECT's query manually and there were records to select.  

I've been doing a little research and discovered that defining a workspace needs to indicate whether it is a Jet workspace or an ODBC workspace.  I got a code example, so I'll try it and let you know how it goes.

jar
0
 
JudithARyanTech SupportAuthor Commented:
This wasn't a complete solution but got me several major steps closer.  Since I stuck on a different project for another week, I wanted to give MV credit now.  I'll post a followup question in a few weeks.

Thanks for your help.

J
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.