?
Solved

runtime error  on vb odbc connection function

Posted on 2012-04-04
14
Medium Priority
?
395 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 2000 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

764 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