LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1882
Members Online:
2
Guests Online:
84

Total Post History
Posts:
81456
Topics:
18718

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
justanails28

Other

FAQ
All times are UTC [ DST ]

FYI Excel, Recordsets, MySQL

Please wait...

FYI Excel, Recordsets, MySQL

Postby Remou on Thu May 13, 2010 10:02 pm

If you are creating a table in MySQL, you may, if your previous experience is Access and Excel, just opt for a text data type with an auto increment ID. If you the try to update the recordset, you will run into all sorts of problems.

Here are some notes to try.

In MySQL
MySQL>CREATE TABLE Test (ID Int AUTO_INCREMENT PRIMARY KEY, Data1 Text, Data2 Text);

In Excel
  1. Dim rs As New ADODB.Recordset
  2. Dim cn As New ADODB.Connection
  3.    
  4.     strCon = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=phaedra;" _
  5.     & "User=root;Password=lob539fgh;Option=3;"
  6.  
  7.     cn.Open strCon
  8.  
  9.     rs.Open "Select ID,Data1,Data2 " _
  10.     & "From Test", cn, adOpenKeyset, adLockOptimistic
  11.  
  12.     With rs
  13.         .AddNew ' create a new record
  14.         ' add values to each field in the record
  15.         .Fields("Data1") = "abc"
  16.         .Fields("Data2") = "def"
  17.         .Update
  18.     End With


In MySQL
mysql> select * from test;
+----+-------+-------+
| ID | data1 | data2 |
+----+-------+-------+
| 1 | NULL | def |
+----+-------+-------+

No matter how many fields (columns) you have, only the last one updates. I found that messing about with debugging could crash Excel.

The moral of the story is avoid text data type.
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated