Hi there

I was wondering if anybody knew how to change the below code (which currently defaults to a MS Access database) to a mysql database.

The below code is apart of a Caller ID software

Private Sub OpenDataBase()
    Set fo = New FileSystemObject
    Set wrk = CreateWorkspace("", "admin", "", dbUseJet)
    
    If Not fo.FileExists(m_stDataPath & "callerid.mdb") Then
        Set db = wrk.CreateDatabase(m_stDataPath & "callerid.mdb", dbLangGeneral)
        CreatePhoneDB
    Else
        Set db = wrk.OpenDataBase(m_stDataPath & "callerid.mdb")
    End If
    Set rs = db.OpenRecordset("PhoneCalls", dbOpenTable)
    
End Sub

Private Sub CloseDatabase()
    db.Close
    wrk.Close
    Set db = Nothing
    Set wrk = Nothing
    Set fo = Nothing
End Sub


Private Sub AddRecord()
    Dim lID As Long
    Select Case Len(frmLineInfo.Number.Text)
      Case 0
        frmLineInfo.Number.Text = "No Number"
      Case 1
        Select Case frmLineInfo.Number.Text
          Case "O"
            frmLineInfo.Number.Text = "Unavailable"
            frmLineInfo.CallName.Text = "Unavailable"
          Case "P"
            frmLineInfo.Number.Text = "Blocked"
            frmLineInfo.CallName.Text = "Blocked"
       End Select
    End Select
    Select Case Len(frmLineInfo.CallName.Text)
      Case 0
        frmLineInfo.CallName.Text = "-"
      Case 1
        Select Case frmLineInfo.CallName.Text
          Case "O"
            frmLineInfo.CallName.Text = "Unavailable"
          Case "P"
            frmLineInfo.CallName.Text = "Blocked"
        End Select
    End Select
    
    With rs
        If (.RecordCount > 0) Then
            .MoveLast
            lID = .Fields("id") + 1
        Else
            lID = 1
        End If
        .AddNew
        .Fields("id") = lID
        .Fields("datetime") = Now
        .Fields("number") = frmLineInfo.Number.Text
        .Fields("name") = frmLineInfo.CallName.Text
        .Update
    End With

End Sub

Private Function CreatePhoneDB() As Recordset
    Dim tbl As TableDef
    Set tbl = db.CreateTableDef("PhoneCalls")
    With tbl
        .Fields.Append .CreateField("id", dbLong, 4)
        .Fields.Append .CreateField("datetime", dbDate, 4)
        .Fields.Append .CreateField("number", dbText, 20)
        .Fields.Append .CreateField("name", dbText, 20)
        db.TableDefs.Append tbl
    End With
    Set db = wrk.OpenDataBase(m_stDataPath & "callerid.mdb")
    Set tbl = db!phonecalls
    Set idx = tbl.CreateIndex("DateTime")
    idx.Fields.Append idx.CreateField("datetime")
    tbl.Indexes.Append idx

    
End Function
    
Private Sub Report_Click()
    Load frmReport
    frmReport.Show
End Sub

Do you mean that instead of MS Access database as back-end, use the MS SQL? Weill if that's the case then you have to change the connecstring of the database

hi

yes thats what I mean, change the backend to mysql database :o)

This article has been dead for over six months. Start a new discussion instead.