Retrieve Data with VBA Access

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

Open in new window


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"

Open in new window


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)
    
    rs.MoveFirst
    Do While Not rs.EOF
        Print rs.Fields("ID")
        rs.MoveNext
        
    Loop
        
    rs.Close
    

End Sub

Open in new window

APD TorontoSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
If you post an actual copy of the table, we can see better how the data is stored and can be more helpful on offering a solution.

To properly normalize the data, you don't want a comma separated string, you actually want a second table with 1-3 rows for each row in the main table.

Export the table to a .csv  or .xlsx file that we can work with.  If it is very large, cut it down to a thousand records.  The code will depend on how consistently formatted the multiple lines are.

PS - copy and paste is almost always the wrong method to transfer data between Access and Excel.  You may find that simply exporting using the TransferText or TransferSpreadsheet methods (you can do these using the right-click menu without code also) may actually solve the problem without code.
0
BitsqueezerCommented:
Hi,

yes, in the VBA editor select "Tools" - "References", there you must add a reference to "Microsoft ActiveX Data Objects 6.x Library" (in Windows XP or lower it is 2.8).

Your code is more or less showing the DAO method to open a recordset. Usually you use DAO in Access, ADO is not needed. So I would recommend, just for this job, to use DAO instead which would be slightly different (and you would not need to set a reference):

Option Compare Database
Option Explicit

Private Sub cmdInterests_Click()

    Dim rs As DAO.Recordset
    Dim sql As String
    sql = "SELECT * FROM TBL_DICE_Factorial_Analysis"
    
    Set rs = CurrentDb.OpenRecordset(sql)
    
    rs.MoveFirst
    Do While Not rs.EOF
        Print rs.Fields("ID")
        rs.MoveNext
        
    Loop

    rs.Close
End Sub

Open in new window


Cheers,

Christian
0
APD TorontoSoftware DeveloperAuthor Commented:
Here is the Excel export.

As you'll see record 1 starts on Row 1 (good), but record 2 startson Row 4; Rec 3 on Row 7; and so on... The issue is Columns EFG, as in Access they are Memo fields, then separated by carriage return.
noc_factorial.xlsx
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

PatHartmanCommented:
Did you create the export using the method I suggested?

Right click on table name.  Choose Export/Excel
0
BitsqueezerCommented:
Hi,

if you use the VBA loop you could copy the Memo text into a string variable and then use the Replace function to replace the vbCrLf to a comma string to get the output you want. You could open a text file and write the values of the records into that file using the Open and Print# commands.

Cheers,

Christian
0
APD TorontoSoftware DeveloperAuthor Commented:
Christian, the code helped a bit, but now I get the Print method is not supported. When I try to output it to a textbox, I get that the Length property (which I cannot find) is too short.  Essentially, I'm trying to build my own csv string

My code now
Option Compare Database
Option Explicit

Private Sub cmdInterests_Click()

    Dim rs As DAO.Recordset
    Dim sql As String
    sql = "SELECT * FROM TBL_DICE_Factorial_Analysis"
    
    Set rs = CurrentDb.OpenRecordset(sql)
    
    Dim txt As String
    txt = ""
    
    rs.MoveFirst
    Do While Not rs.EOF
        txt = txt & rs.Fields("ID") & vbCrLf
        
        rs.MoveNext
        
    Loop

    rs.Close
    
    txtResult.SetFocus
    txtResult.Text = txt
    
End Sub

Open in new window


Even, if I do ?txt  in the Immediate window, I only get the last 20  lines. Not  1 to 1374
0
BitsqueezerCommented:
You need to use "Debug.Print" if you want to output the text to the Immediate Window of VBA.

If you want to add one line like with your first example, it would be rather something like this:

txt = txt & rs.Fields("ID") & "," & rs.Fields("name") & "," & Replace(rs.Fields("Properties"),vbCrLf, ",") & vbCrLf

Open in new window


But of course the Immediate Window has only a small buffer, that's why I said you would need to output the CSV to a file:

Dim f As Integer
f = FreeFile
Open "filename.csv" For Output As #f
....
' (in the loop then after building the CSV string)
Print #f, txt
...
' after ending the loop:
Close #f

Open in new window

In case that you are writing to a file you will see all rows, moreover you should remove the last "vbCrLf" from the txt string above as "Print #" automatically writes a return to the file if you do not add a ";" at the end of the Print line.
0
APD TorontoSoftware DeveloperAuthor Commented:
Exporting by right click helping get better results, but I cannot still import.

The attached is what I ended up with exporting, columns A through H, then after unsuccessful import I tried building the formula on J1, but now all carriage return no space.  Within my Excel formula, can I do a replace of e1 so that it replace a carriage return with ", "?
0
APD TorontoSoftware DeveloperAuthor Commented:
Here is the file...
TBL_DICE_Factorial_Analysis.xlsx
0
Chris StanyonWebDevCommented:
Have you considered doing all of this directly in PHP ? Make 2 database connections - one to your Access DB and one to your MySQL database. Then just run your SELECT query against the Access connection, loop through the records, do whatever manipulation you need (str_replace the newlines with commas / create child records etc) and run an INSERT query against your MySQL connection.
0
APD TorontoSoftware DeveloperAuthor Commented:
I wanted to limit my work, but I can do that if I can connect PHP to Access. What is the PHP connection string for Access?
0
Chris StanyonWebDevCommented:
The PDO Connection code would look something like this:

$dbName = "c:\path\to\your\database.mdb";
$dsn = "odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=$dbName;";
$access = new PDO($dsn);

Open in new window

You'll need to ensure you have the PDO ODBC driver enabled in PHP (uncomment the extension=php_pdo_odbc.dll line in php.ini)

Even if you decide not to do the MySQL INSERT queries at this point, it may still be easier to build your CSV file using PHP if you're more familiar with it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
You can do what you need directly in the Access query.  Here's the Access query

SELECT TBL_DICE_Factorial_Analysis.ID, TBL_DICE_Factorial_Analysis.Field2, TBL_DICE_Factorial_Analysis.Title, Replace([Set1],Chr(10),", ") AS Expr1, Replace([Set2],Chr(10),", ") AS Expr2, Replace([Set3],Chr(10),", ") AS Expr3
FROM TBL_DICE_Factorial_Analysis;


If MySQL has a replace() function AND some way to pass in an ASCII character, you can do it in MySQL.  Otherwise, create an Access querydef and try to execute that from PHP.
Query.JPGResults.JPG
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.