Solved

Simple MDB to CSV Conversion

Posted on 2001-09-08
12
361 Views
Last Modified: 2010-08-05
I just want to know 2 questions.

1) How to convert a mdb database to csv format.

2) How to make  row of the datagrid to be highlighted through vb code.

g_handa
0
Comment
Question by:g_handa
  • 6
  • 6
12 Comments
 

Author Comment

by:g_handa
ID: 6466463
asap

g_handa
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6466733
Step 1 - set up a Project Reference to DAO

Step 2 - create a command button: command1

Step 3 - paste the following code

The code will export either all tables our just one table

You will need to edit the code with your database name
and destination folder where the cvs files will end up.

Hope this helps.


Private Sub Command1_Click()

Dim DB As DAO.Database
Dim TD As DAO.TableDef
Dim FLD As DAO.Field
Dim RS As DAO.Recordset


Dim fh As Long ' file handle
Dim fldc As Long ' fieldcounter
Dim tn As String ' table name
Dim dt As String ' converted field data

'open the database
Set DB = DBEngine.OpenDatabase("C:\yourdb.mdb")

' Export all tables
For Each TD In DB.TableDefs
    GoSub ExportTable
Next

' Export just one
Set TD = DB.TableDefs("Your Table")
GoSub ExportTable

DB.Close
Set DB = Nothing

Exit Sub

ExportTable:
   
    ' open the csv file.
   
    tn = TD.Name ' table name
   
    fh = FreeFile
    Open "c:\yourfolder\" + tn + ".CSV" For Output As fh
       
    ' create field headings
    ' e.g.
    ' "Field1", "Field2", "fieldetc",
    For fldc = 0 To TD.Fields.Count - 1
       
        If fldc > 0 Then
            Print #fh, ", ";
        End If
        Print #fh, Chr$(33) + TD.Fields(fldc).Name + Chr$(33);
       
    Next
       
    Print #fh, "" ' end of heading line
   
    ' now get the data
   
    Set RS = DB.OpenRecordset("Select * from [" + tn + "];", dbOpenSnapshot)
   
    ' loop through all records
   
    Do While Not RS.EOF
       
        ' Loop through all of the fields    
        For fldc = 0 To RS.Fields.Count - 1
           
            If fldc > 0 Then
                Print #fh, ", ";
            End If
           
            Set FLD = RS.Fields(fldc)
           
            ' now write the data
            Select Case FLD.Type
                Case Is = dbText
                    If IsNull(FLD) Then
                        dt = ""
                    Else
                        dt = FLD
                    End If
                   
                    Print #fh, Chr$(33) + dt + Chr$(33);
                Case Is = dbMemo
                    If IsNull(FLD) Then
                        dt = ""
                    Else
                        dt = FLD
                    End If
                   
                    Print #fh, Chr$(33) + dt + Chr$(33);
                Case Is = dbDate
                    If IsNull(FLD) Then
                        dt = ""
                    Else
                        dt = Format(dt, "Medium Date") ' you may need to change this
                        ' e.g. DD-MMM-YY HH:NN:SS ' FOR EURO DATES AND TIMES
                    End If
                   
                    Print #fh, dt;
                Case Else ' numerics
                    If IsNull(FLD) Then
                        dt = "0"
                    Else
                        dt = CStr(dt)
                    End If
                   
                    Print #fh, dt;
            End Select
        Next
       
        Print #fh, "" ' end of row
       
        Set FLD = Nothing
        RS.MoveNext
    Loop
   
   
   
    RS.Close
   
    Set RS = Nothing
   
    Close fh
   
   
Return


End Sub
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6466757
The product docuentation skipped out on how to select a row but it can be done as follows:

YourGrid.SelStartRow=3
YourGrid.SelEndRow=3

' To select the current row

cr=YourGrid.Row
YourGrid.SelStartRow=cr
YourGrid.SelEndRow=cr

Clear the selection by setting to -1

You can also select a region by using the SelStartCol and SelEndCol

Hope this helps.
0
 

Author Comment

by:g_handa
ID: 6466931
thanks for ur fast reply. let me try to run the code.

g_handa
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6467071
You had better test it I just typed it in untested - hope it works.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6467106
I did notice a bug:

Case Is = dbDate
                   If IsNull(FLD) Then
                       dt = ""
                   Else
                       dt = Format(FLD, "Medium Date") ' This was wrong
                   End If
                   
                   Print #fh, dt;
               Case Else ' numerics
                   If IsNull(FLD) Then
                       dt = "0"
                   Else
                       dt = CStr(FLD) ' and this
                   End If
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:g_handa
ID: 6467296
Hello inthedark,

The datagrid control doesn't have the propery of selstartrow and selendrow

What should I do then.
0
 

Author Comment

by:g_handa
ID: 6467299
how the bugs r going to be removed..??
0
 

Author Comment

by:g_handa
ID: 6467305
how the bugs r going to be removed..??
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6467400
When I looked at this reference if had information on the selrowstart property.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/DBGrid98/html/vbprocol.asp

But I think these properties were superceeded by the selbookmarks collection see the following.


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/DBGrid98/html/daproselbookmarks.asp


The bookmark is a string reference to a record in a recordset

I think you can use it like this:


DataGrid1.Rpw=3 ' goto the row you want to select

DataGrid1.SelBookmarks.Add DataGrid1.Bookmark


Before adding a row you may wish to clear a previous selection:

' CLear previous selected records
Do While DataGrid1.SelBookmarks.Count > 0
    DataGrid1.SelBookmarks.Remove 0
Loop

' add the current row
DataGrid1.SelBookmarks.Add DataGrid1.Bookmark


Hope this helps.
0
 
LVL 17

Accepted Solution

by:
inthedark earned 300 total points
ID: 6467420
So here it is tested.  I added some frills.
If you need help understanding it let me know.
Have fun....

Private Sub Command1_Click()

Dim DB As DAO.Database
Dim TD As DAO.TableDef
Dim FLD As DAO.Field
Dim RS As DAO.Recordset


Dim fh As Long ' file handle
Dim fldc As Long ' fieldcounter
Dim tn As String ' table name
Dim dt As String ' converted field data

'open the database
Set DB = DBEngine.OpenDatabase("d:\$vb6\mdbs\webpages.mdb")

' Export all tables
For Each TD In DB.TableDefs
    ' don't export attched tables or System tables
    If Len(TD.SourceTableName) = 0 And UCase(Left(TD.Name, 4)) <> "MSYS" Then
       GoSub ExportTable
    End If
Next

' or Export just one
Set TD = DB.TableDefs("z Server")
GoSub ExportTable

DB.Close
Set DB = Nothing
MsgBox "Done"
Exit Sub

ExportTable:
   
   ' open the csv file.
   
   tn = TD.Name ' table name
   
   fh = FreeFile
   Open "d:\$vb6\mdbs\" + tn + ".CSV" For Output As fh
       
   ' create field headings
   ' e.g.
   ' "Field1", "Field2", "fieldetc",
   For fldc = 0 To TD.Fields.Count - 1
       
       If fldc > 0 Then
           Print #fh, ", ";
       End If
       Print #fh, Chr$(34) + TD.Fields(fldc).Name + Chr$(34);
       
   Next
       
   Print #fh, "" ' end of heading line
   
   ' now get the data
   
   Set RS = DB.OpenRecordset("Select * from [" + tn + "];", dbOpenSnapshot)
   
   ' loop through all records
   
   Do While Not RS.EOF
       
       ' Loop through all of the fields
       For fldc = 0 To RS.Fields.Count - 1
           
           If fldc > 0 Then
               Print #fh, ", ";
           End If
           
           Set FLD = RS.Fields(fldc)
           
           ' now write the data
           Select Case FLD.Type
               Case Is = dbText
                   If IsNull(FLD) Then
                       dt = ""
                   Else
                       dt = Replace(FLD, Chr$(34), "'", 1, -1)
                   End If
                   
                   Print #fh, Chr$(34) + dt + Chr$(34);
               Case Is = dbMemo
                   If IsNull(FLD) Then
                       dt = ""
                   Else
                       dt = Replace(FLD, Chr$(34), "'", 1, -1)
                   End If
                   
                   Print #fh, Chr$(34) + dt + Chr$(34);
               Case Is = dbDate
                   If IsNull(FLD) Then
                       dt = ""
                   Else
                       dt = Format(FLD, "Medium Date") ' you may need to change this
                       ' e.g. DD-MMM-YY HH:NN:SS ' FOR EURO DATES AND TIMES
                   End If
                   
                   Print #fh, dt;
               Case Else ' numerics
                   If IsNull(FLD) Then
                       dt = "0"
                   Else
                       dt = CStr(FLD)
                   End If
                   
                   Print #fh, dt;
           End Select
       Next
       
       Print #fh, "" ' end of row
       
       Set FLD = Nothing
       RS.MoveNext
   Loop
   
   
   
   RS.Close
   
   Set RS = Nothing
   
   Close fh
   
   
Return


End Sub
0
 

Author Comment

by:g_handa
ID: 6468594
you r an excellnt coder

thanks very much
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Updates not working for MS Windows 7 12 152
bit defender blocks good applications 2 79
Using "ScreenUpdating" 6 55
How to measure sizes and angles in scanned images ? 3 51
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now