Solved

runtime error  on vb odbc connection function

Posted on 2012-04-04
14
374 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
  • 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now