Solved

Simple MDB to CSV Conversion

Posted on 2001-09-08
12
363 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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
 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

820 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