Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

Simple MDB to CSV Conversion

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
g_handa
Asked:
g_handa
  • 6
  • 6
1 Solution
 
g_handaAuthor Commented:
asap

g_handa
0
 
inthedarkCommented:
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
 
inthedarkCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
g_handaAuthor Commented:
thanks for ur fast reply. let me try to run the code.

g_handa
0
 
inthedarkCommented:
You had better test it I just typed it in untested - hope it works.
0
 
inthedarkCommented:
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
 
g_handaAuthor Commented:
Hello inthedark,

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

What should I do then.
0
 
g_handaAuthor Commented:
how the bugs r going to be removed..??
0
 
g_handaAuthor Commented:
how the bugs r going to be removed..??
0
 
inthedarkCommented:
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
 
inthedarkCommented:
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
 
g_handaAuthor Commented:
you r an excellnt coder

thanks very much
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now