Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 23rd, 2009, 09:44 PM
JVM JVM is offline
Registered User
 
Join Date: Jan 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Thumbs down Export from Table..with a twist

I wrote a database to help out at the school I teach. The administration software with which I need to connect is very picky. I have a table that looks like this:
Code:
ID     CN
13     eng1001
210    sci1234
13     sci431
19     lang1111
210    lang 1111
13     math1234
etc. ID is the student ID, CN is the course number (alphanumeric) that students requested. I need to dump it into a tab delimted table that looks like this:
Code:
13   eng1001   sci431   math1234
210  lang1111  sci1234
19   lang1111
each student will have from 1 to 8 different courses that all need to appear on the same line. A dump directly to tab-delimited or excel would work.
Any ideas? Thought perhaps I needed a nested loop

thanks in advance for any help you can offer!
Joe
 
Old January 27th, 2009, 09:37 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I have helped people do this in the past. Here is some psuedo code off the top of my head:

Create text file
Close text file
Open text file for Writing (.csv)

Create recordset on table (SELECT DISTINCT ID FROM YourTable)
rs.MoveFirst
Do Until rs.eof
sID = rs("ID")
sString = sID
Create recordset (rs1) on table again (SELECT ID, CN FROM YourTable WHERE ID = sID)
Do Until rs1.eof (there will always be at least one record)
sString = sString & vbTab & rs("CN")
rs1.MoveNext
Loop
objFile.WriteLine sString

rs.MoveNext
Loop

rs,Close
rs1.Close
objFile.Close

This will take this data:

ID CN
1 eng101
1 eng102
2 math101
2 math102

and make this:

1 eng101 eng102
2 math101 math102

in a csv file.

How much of the code do you need?
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old January 27th, 2009, 09:59 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Here is the code that I came up with to do this:

Code:
Dim sString As String
Dim rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim sSQL As String
Dim sSQL1 As String
Dim lID As Long
Dim objFile As Variant
Dim objFSO As Variant
Const ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("C:\SampleTextFile.csv")
objFile.Close
Set objFile = objFSO.OpenTextFile("C:\SampleTextFile.csv", ForAppending)
 
sSQL = "SELECT DISTINCT ID FROM Table1 ORDER BY ID"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.MoveFirst
Do Until rs.EOF
    lID = rs("ID")
    sString = lID
    sSQL1 = "SELECT * FROM Table1 WHERE [ID] = " & lID & " ORDER BY [CN]"
    Set rs1 = New ADODB.Recordset
    rs1.Open sSQL1, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    Do Until rs1.EOF
        sString = sString & ", " & rs1("CN")
 
    rs1.MoveNext
    Loop
        objFile.WriteLine sString
        sString = ""
rs.MoveNext
Loop
This is the starting data:

Table1
ID CN
1 eng101
1 eng102
2 math101
2 math102
2 eng101
3 fren101
3 math101
4 bus101
4 span101

This is the result in Excel:

1 eng101 eng102
2 eng101 math101 math102
3 fren101 math101
4 bus101 span101

Well, each one of those is in an individual cell. I use comma delimited since tabs were not working correctly.

Did that help?
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old January 30th, 2009, 09:52 PM
JVM JVM is offline
Registered User
 
Join Date: Jan 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default THANKS

Fantastic..JUST what I needed.
I really appreciate the help!
Joe





Similar Threads
Thread Thread Starter Forum Replies Last Post
Export a Table into Word hewstone999 Access VBA 0 March 20th, 2008 06:28 AM
Export Table data into an Excel SpreadSheet hewstone999 Access VBA 0 March 3rd, 2008 07:00 AM
Export table with Specs echovue Access 3 December 25th, 2005 01:16 PM
Export Table from Access to Excel aramchan Access VBA 2 July 12th, 2004 03:20 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.