ADODB.Connection - How do I open a DB, create if not there, open table, create if not there and add a single record?

[Don Anthony]

This should be simple, no?  I can't make it work????

Objective:

- bring up a panel to ask for MySQL User/Host/Password and save settings if
we get logged in.
- when we start (likely on a new machine) create a new Weather DB if it
doesn't exist
- when we start (""  """) create a new TimeTable table if it doesn't exist
- loop through our one per hour loop and add new records as we go

I know this code is likely trash but it is my first attempt and I can find
no example how to do what I think should be simple.  THANKS!!!!!!!!!!


Main.bas:  (contains global vars)
        Global gCnn As ADODB.Connection
        Global pRs As ADODB.Recordset
        Global SQL As String, UserName As String, UserPassword As String,
UserHost As String

Connect.frm:  (loaded on startup - verifies connection)
  Private Sub ClickConnect:
    Set gCnn = New ADODB.Connection
    Set pRs = New ADODB.Recordset
    
    gCnn.ConnectionTimeout = 60
    gCnn.CommandTimeout = 400
    gCnn.CursorLocation = adUseClient
    
    gCnn.Open "DRIVER={MySQL ODBC 3.51 Driver};" _
        & "user=" & UserName _
        & ";password=" & UserPassword _
        & ";server=" & UserHost _
        & ";option=20499"
        
    If gCnn.State = 1 Then
        SaveSetting App.Title, "Settings", "UserHost", UserHost
        SaveSetting App.Title, "Settings", "UserName", UserName
        SaveSetting App.Title, "Settings", "UserPassword", UserPassword
        frmWeather.Show vbModal
        Unload Me  'we logged on OK, so we will save this info for next
logon
    Else
        MsgBox "Unable to establish the connection. Check your settings and
try again.", vbCritical, "Error While Connecting"
          End
    End If

frmWeather (Main Form):
  FormLoad:
    gCnn.Open "Weather"
    If gCnn.State <> 1 Then 'create database if it does not exist
         gCnn.Execute "Create Database Weather", , adExecuteNoRecords
         gCnn.Open "Weather"
         If gCnn.State <> 1 Then
              MsgBox "Failed to access Weather DB"
              End
         End If
    End If
    On Error GoTo loadstp
        
    SQL = "CREATE TABLE IF NOT EXISTS `TimeTable` (" _
        & "`Time` varchar(30) NOT NULL default '', " _
        & "`Temperature` varchar(20) NOT NULL default '', " _
        & "PRIMARY KEY  (`Time`) " _
        & ") TYPE=MyISAM"
    gCnn.Execute SQL, , adExecuteNoRecords 'create table if not exist
    
    
    SQL = "SELECT * FROM TimeTable"
    
     pRs.Open SQL, gCnn, adOpenDynamic, adLockOptimistic, adCmdText

     [loop logic to loop every hour]
       With pRs
        .AddNew
        .Fields("Time") = Time()
        .Fields("Temperature") = fromSendor
        .Update
       End With
     [end loop logic]   
   
     Exit Sub
loadstp:
    MsgBox Err.Description
End Sub



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]