UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dao And Mysql In Windows Micro Xp.    
 
   
panoss
post Sep 22 2015, 10:33 AM
Post#1



Posts: 46
Joined: 6-November 14



I have a relatively old pc where I installed micro XP (a 'slim' version of Windows XP).

I want to connect to MySQL with VBA. (Access 2007)
But it has a problem with ADO of VBA, so I 'm trying to use DAO. (in normal XP ADO and MySQL work fine, but here I have to use this micro XP)
Somewhere in the internet, I read that I should use an older database engine but it didn't succeed.

(just informatively, I can use a MySQL table in Access: External Data > Link to the data Source by creating a linked table. But I want to do it with code. So that I won't have to link a tableto use it)

CODE
    Dim daoDbEngine36 As Object
            
            Set daoDbEngine36 = CreateObject("DAO.DBEngine.36")
            Set wkspc = daoDbEngine36.CreateWorkspace("", "", "", dbUseODBC)
      
            Set cn = wkspc.OpenConnection("", , , _
                                       "Driver={MySQL ODBC 5.3 Unicode Driver};" _
                                     & "Server=localhost;Port=3306;" _
                                     & "Database=joomla343;" _
                                     & "User=root;" _
                                     & "Password="";" _
                                     & "Option=3;")
        
            Set rs = cn.OpenRecordset("SELECT * FROM " & MyTable, dbOpenSnapshot)



But it gives me an error at "Set wkspc = daoDbEngine36.CreateWorkspace("", "", "", dbUseODBC)": Error 3847: ODBC is no longer supported. Rewrite the code to use ADO....

So, there is no way to use DAO?
Go to the top of the page
 
panoss
post Sep 25 2015, 06:33 AM
Post#2



Posts: 46
Joined: 6-November 14



I still haven't managed to connect to my sql (:. The code I try now is with ADO:
CODE
    Dim strDataBaseName As String
      Dim strDBCursorType As String
      Dim strDBLockType As String
      Dim strDBOptions As String
      Dim rs As ADODB.Recordset
      Dim cn As ADODB.Connection
  
      On Error GoTo Command1_Click_Error
      Dim b As Long
      strDBCursorType = adOpenDynamic  'CursorType
      strDBLockType = adLockOptimistic   'LockType
      strDBOptions = adCmdText         'Options
  
      Set cn = CreateObject("ADODB.Connection")


The error is raised at: Set cn = CreateObject("ADODB.Connection")

Error 429 activex can't create object.

My references are:
Visual Basic for Applications
Microsoft Access 12.0 object library
OLE automation
Microsoft Office 12.0 Access database engine
Microsoft ActiveX Data Objects 2.7 Library

I tried many things I found in the internet but nothing worked.
Any suggestion is wellcome.
Go to the top of the page
 
panoss
post Sep 25 2015, 09:53 AM
Post#3



Posts: 46
Joined: 6-November 14



FinallY!!! I managed to work out with DAO!!! woohoo.gif (from here):


CODE
strOdbcCon="ODBC" _
& ";DRIVER=SQL Server" -
& ";SERVER=YourServer" _
&  ";DATABASE=YourDatabase" _
& ";uid=" & strUserName _
& ";pwd=" & strPassword

strSQL="SELECT * FROM [" & strOdbcCon & "].OneTable WHERE FALSE"
set db=currentdb
set rs=dbs.openrecordset(strSQL,dbOpenNapshot)
Go to the top of the page
 
panoss
post Sep 26 2015, 02:11 AM
Post#4



Posts: 46
Joined: 6-November 14



A better way:
CODE
Public Function InitConnect() As Boolean
    On Error GoTo ErrHandler

    Dim dbCurrent As DAO.Database
    Dim qdf As DAO.queryDef
    Dim rst As DAO.Recordset
    ServerAddress = "localhost": PortNum = "3306": Opt = "3": DbName = "joomla343": UserName = "root": Password = ""

    strConnection = "ODBC;DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
                    "Server=" & ServerAddress & ";" & _
                    "Port=" & PortNum & ";" & _
                    "Option=" & Opt & ";" & _
                    "Stmt=;" & _
                    "Database=" & DbName & ";"

    Set dbCurrent = DBEngine(0)(0)
    Set qdf = dbCurrent.CreateQueryDef("")

    With qdf
        .Connect = strConnection & _
                   "Uid=" & UserName & ";" & _
                   "Pwd = " & Password
        .SQL = "SHOW TABLES;"
        Set rst = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
    End With
    
    rst.MoveFirst
    
    Do Until rst.EOF
    
        Debug.Print rst.Fields(0)
        
        rst.MoveNext
    Loop
    
    InitConnect = True

ExitProcedure:
    On Error Resume Next
    Set rst = Nothing
    Set qdf = Nothing
    Set dbCurrent = Nothing
    Exit Function
ErrHandler:
    InitConnect = False
    MsgBox Err.Description & " (" & Err.Number & ") encountered", _
           vbOKOnly + vbCritical, "InitConnect"
    Resume ExitProcedure
    Resume
End Function
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 07:54 AM