Solved

runtime error  on vb odbc connection function

Posted on 2012-04-04
14
386 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:JudithARyan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 37808205
Try: "DSN=odbc2mysql; UID=nnnn; PWD=nnnn3!; DATABASE=xxxx;"
0
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 37808215
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
 

Author Comment

by:JudithARyan
ID: 37812665
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 37812937
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
 

Author Comment

by:JudithARyan
ID: 37814366
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
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 37814381
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
 

Author Comment

by:JudithARyan
ID: 37824287
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
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 37825311
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
 

Author Comment

by:JudithARyan
ID: 37825420
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
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 37825476
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
 

Author Comment

by:JudithARyan
ID: 37825720
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
 
LVL 10

Accepted Solution

by:
Michael Vasilevsky earned 500 total points
ID: 37825753
No message box at all? Are you sure that code is firing? rs2 isn't empty is it?
0
 

Author Comment

by:JudithARyan
ID: 37828688
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
 

Author Closing Comment

by:JudithARyan
ID: 37862763
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question