Problem with DBConnect/DBQuery and Access

migro · Post by **migro** » Mon Jun 22, 2009 6:35 pm

When I try to connect a access db I get following error:

"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

What do I need to specify more here?

Code: Select all

Let>strRKB=C:\MyDB.mdb
Gosub>GetDr

SRT>GetDr
   //Connect to Datasource
   ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%strRKB%;
   DBConnect>ConStr,dbH
 
   //Perform SELECT query
   Let>SQL=SELECT DISTINCT tXraySDr FROM TXray
   DBQuery>dbh,SQL,Dentist,numrecs,numfields
 
   //loop through returned recordset
   Let>r=0
   Repeat>r
   Let>r=r+1
   Let>f=0
   Repeat>f
      Let>f=f+1
      Let>this_field=Dentist_%r%_%f%
   Message>this_field

   Wait>0.5
   Until>f=numfields
   Until>r=numrecs
 
   //Close database connection
DBClose>dbH
END>GetDr

Bob Hansen · Post by **Bob Hansen** » Mon Jun 22, 2009 9:56 pm

Have you actually created an ODBC connection to the Access database?

Is the name you used in your script spelled correctly? Looks like an OLE vs. ODBC line.

migro · Post by **migro** » Mon Jun 22, 2009 10:37 pm

I like to use OLE DB without creating a datasource

When I connect with VB it looks like:

Code: Select all

SRT>GetDr

Let>strRKB=C:\MyDB.mdb

Vbstart
   Function GetXvalue(strRKB)
   Dim Sqlstring
   Dim RS

   'On Error Resume Next
   Set Mydb = Createobject("Adodb.Connection")
   Set RS = CreateObject("ADODB.recordset")
   Mydb.Provider = "Microsoft.Jet.Oledb.4.0"
   Mydb.Connectionstring = "Data Source=" & strRKB & ";"
   Mydb.Open
      Sqlstring = "SELECT DISTINCT tXraySDr FROM TXray"
      Set RS = Mydb.Execute(Sqlstring)
      If Not RS.Eof Then
         GetXvalue = RS.Fields("tXraySDr")
      Else
         GetXvalue = "Not Found"
      End If

   RS.Close
   Mydb.Close
   End Function
Vbend

Vbeval>GetXvalue("%strRKB%"),strValue
MessageModal>%strValue%
End>GetDr

This works well, but I don't have any idea how to get the data from the recordset into a Macroscheduler variable. So i tried it with dbconnect/dbquery.

But I think there is someting wrong with the connectstring.

Post by **Marcus Tettmar** » Wed Jun 24, 2009 7:53 am

The following may help:

http://www.mjtnet.com/blog/2008/04/15/u ... functions/

It includes a link to a website on connection strings where you should find the format you need for your database. But the best place to look is your documentation.

migro · Post by **migro** » Wed Jun 24, 2009 11:51 pm

Thank you Marcus,

The connection string I used is from your weblink.

In between I made with VB like this (maybe the code is helpfull for someone who need to do the same):

Code: Select all

SRT>GetDr

Let>strRKB=C:\MyDB.mdb

Vbstart
   Function GetXvalue(strRKB)
   Dim Sqlstring
   Dim RS
   Dim VSTR

   On Error Resume Next
   Set Mydb = Createobject("Adodb.Connection")
   Set RS = CreateObject("ADODB.recordset")
   Mydb.Provider = "Microsoft.Jet.Oledb.4.0"
   Mydb.Connectionstring = "Data Source=" & strRKB & ";"
   Mydb.Open
      Sqlstring = "SELECT DISTINCT tXraySDr FROM TXray"
      Set RS = Mydb.Execute(Sqlstring)
      If Not RS.Eof Then
    	   While RS.EOF = False
            VSTR = VSTR & RS.Fields("tXraySDr") & "%CRLF%"
            RS.MoveNext
         Wend
  	     GetXvalue = VSTR
      Else
         GetXvalue = "Not Found"
      End If
   RS.Close
   Mydb.Close
   End Function
Vbend

Vbeval>GetXvalue("%strRKB%"),strDr
   Length>%strDr%,Len
   Add>Len,-1
   MidStr>%strDr%,3,%Len%,somevalue
   Let>strDr=Alle Behandler%CRLF%%somevalue%
End>GetDr

But it would be nice to know, why its not possible to use the MS DBConnect/DBQuery with OLE-DB. As You can see in the VB-Code there is no password or anything else needed to connect to my access-db.