troubleshooting Question

Retrieve Data with VBA Access

Avatar of APD Toronto
APD TorontoFlag for Canada asked on
PHPMicrosoft AccessVBA
13 Comments2 Solutions111 ViewsLast Modified:
Hi Experts,

Let me start off by saying that I have not touched Access and VBA for 5 years, asI have re-focused on PHP/MySQL.

I need to transfer from Access to MySQL, but the previous developer poorly structured the db. For example, each record can have one to three different properties. So, instead of linked tables, they used Memo fields and the data looks like:

id  name   properties
1   chief    prop 1
                  prop 2
                  prop 3
2   mech   prop 1
                  prop 2

From table view, when I copy and paste into Excel, I end up with 5 rows, instead  of 2.

With VBA (unless there's a faster way), I'm hoping to replace all carriage returns with commas, and end up with the following csv:

id  name   properties
1,   chief,    "prop 1, prop 2, prop 3"
2,   mech,   "prop 1, prop 2"

I know this isn't linked tables neither, but at least it will be importable to mySQL, then I can use PHP to work with the properties as there are only 3 possible properties.  

My VBA so far is below, which I combined from Google, but I ghet User type not defined for ADODB.  I think I need to add a reference to ADODB through some menu option?

Option Compare Database
Option Explicit

Private Sub cmdInterests_Click()

    Dim con As New adodb.Connection
    Dim rs As New adodb.Recordset
    Set con = CurrentProject.Connection
    'Dim rs As Recordset
    'Set rs = Recordset
    Dim sql As String
    sql = "SELECT * FROM TBL_DICE_Factorial_Analysis"
    Set rs = con.Execute(sql)
    Do While Not rs.EOF
        Print rs.Fields("ID")

End Sub
Join our community to see this answer!
Unlock 2 Answers and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros