how-to use Excel to upload records to a MySQL database?

[Guest]

Hi, I'm trying to figure out the best way to use Excel to upload data to a
MySQL database. I want to use Excel (rather than Access) because of its
general purpose edit and calc capability. I've seen reams of articles on how
to import FROM a database. But, I want to push TO a database. Is this
possible? If so, what is the best way?

By the way, I already know how to csv out a file and use Perl under *nix to
push it to the database. What I am looking for is a way to do this inside of
Excel. I already have a DSN set up for my MySQL ODBC driver connection and
can query the database from within Excel. Any pointers, articles, or book
references would be greatly appreciated.

Thanks,

Chris

 

[Guest]

Here is how I send a record to an Access Database. You need to reference
ActiveX Data objects library to use this... The lonly difference for you to
connecting to MySQL.

Option Explicit

'Declare Module Level Constants
Private Const m_cDBLocation As String = "\\C:\Forecast.mdb"
Private Const m_cLogFile As String = "tblLogFile"

Public Sub LogIn()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset

' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & m_cDBLocation & ";"

rst.Open m_cLogFile, cnt, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
rst.AddNew

rst.Fields("User Name") = "Me"
rst.Fields("In As") = "Myself"
rst.Fields("Logged") = "In"
rst.Fields("Time") = Now()

rst.Update

' Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub

 

[Jamie Collins]

Here is how I send a record to an Access Database. <<snipped>>

If that's one approach then here's another, using SQL DML:

Option Explicit

'Declare Module Level Constants
Private Const m_cDBLocation As String = "\\C:\Forecast.mdb"
Private Const m_cLogFile As String = "tblLogFile"

Public Sub LogIn()
Dim cnt As Object
Set cnt = CreateObject("ADODB.Connection")

' Open connection to the database
With cnt
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & m_cDBLocation
.Open
.Execute _
"INSERT INTO " & m_cLogFile & _
" ([User Name], [In As], Logged, [Time])" & _
" VALUES ('Me', 'Myself', 'In', NOW);"
.Close
End With
End Sub

Jamie.

--

 

[Guest]

Thanks Jim and Jamie. This is what I was looking for. I think I like the
method of writing my own SQL statements the best. I just wasn't sure how to
pass them through.

I appreciate the input.

chris

Here is how I send a record to an Access Database. <<snipped>>

If that's one approach then here's another, using SQL DML:

Option Explicit

'Declare Module Level Constants
Private Const m_cDBLocation As String = "\\C:\Forecast.mdb"
Private Const m_cLogFile As String = "tblLogFile"

Public Sub LogIn()
Dim cnt As Object
Set cnt = CreateObject("ADODB.Connection")

' Open connection to the database
With cnt
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & m_cDBLocation
.Open
.Execute _
"INSERT INTO " & m_cLogFile & _
" ([User Name], [In As], Logged, [Time])" & _
" VALUES ('Me', 'Myself', 'In', NOW);"
.Close
End With
End Sub

Jamie.