The Fast and easy way 2 dump Mdb table to TXT

itayzoro · February 6, 2008

Is there a way 2 dump Mdb Table (not all Tables [which i choose]) 2 a simple txt or html without use any Dll UDF enc.. Just export Table

10nx

BTW if there isnt a way and we have 2 use/Call Dll or UDF its ok i just need a WORKING sample (and yes i run a SEARCH on it)

ptrex · February 6, 2008

@itayzoro

Maybe this should get you going.

_ADO UDF

regards

ptrex

Edited February 6, 2008 by ptrex

itayzoro · February 6, 2008

My Dear ptrex what Geo done is gr8 thing but i need to Export table with all the Fields :

ID TIME_START TIME_END DEVICE IN OUT DEVICE_SUB1 ----> this is the Table

1 02/04/2008 02/04/2008 3 1 0 6 ----> THIS WHAT I NEED 2 INCLUDE THE TABLE

1 02/04/2008 02/04/2008 3 0 5 0 ----> AND THIS (The Fields).......

1 02/04/2008 02/04/2008 3 0 0 2

1 02/04/2008 02/04/2008 3 0 2 0

Geo offer only the Table I need Both and Export 2 TXT or Html

itayzoro · February 6, 2008

CAN SOME1 PLZ PLZ HLP

ptrex · February 6, 2008

@

It's not TXT but XLS

; Initialize error handler 
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

ExportTableToOneExcel("C:\db1.mdb",  "C:\Test.xls","TableName")

Func ExportTableToOneExcel($AccName, $ExpExcelName, $tableName = "", $DbPassWord = "")
    Const $adSchemaTables = 20
    Const $acExport = 1
    Const $acSchemaXSD = 1
    Const $acSpreadsheetTypeExcel9 = 8
    
    Local $i, $rstSchema, $accApp, $cnnTemp
    $i = 1
    
    $accApp = ObjCreate("Access.Application")
    $accApp.OpenCurrentDatabase($AccName ) ; $DbPassWord
    ; $accApp.Visible = True

    $cnnTemp = $accApp.CurrentProject.Connection
    $rstSchema = $cnnTemp.OpenSchema($adSchemaTables) 
    Do
        If $rstSchema($i).Type =  202 Then ; "TABLE"
            ConsoleWrite("TABLE_TYPE " & $rstSchema($i).Type & @CRLF)
            For $i = 0 To $rstSchema.Fields.Count - 1
                ConsoleWrite($rstSchema.Fields.Count - 1 & @CRLF)
                If $rstSchema($i).Name = "TABLE_CATALOG"  Then ; 
                 ConsoleWrite($rstSchema($i).Name & @CRLF)
                    If $tableName <> "" Then
                        ;If $rstSchema.Fields($i).Value = $tableName Then
                            $accApp.DoCmd.TransferSpreadsheet($acExport, $acSpreadsheetTypeExcel9, $tableName, $ExpExcelName, True)
                            ;ConsoleWrite($accApp.DoCmd.TransferSpreadsheet($acExport, $acSpreadsheetTypeExcel9, $tableName, $ExpExcelName, True) & @CRLF)
                            ExitLoop
                       ; EndIf
                    Else
                        $accApp.DoCmd.TransferSpreadsheet($acExport, $acSpreadsheetTypeExcel9, $rstSchema.Fields($i).Value, $ExpExcelName, True)
                        ;ConsoleWrite($accApp.DoCmd.TransferSpreadsheet($acExport, $acSpreadsheetTypeExcel9, $rstSchema.Fields($i).Value, $ExpExcelName, True) & @CRLF)
                    EndIf
                EndIf
            Next
        EndIf
        $rstSchema.MoveNext
    Until $rstSchema.EOF
    $rstSchema.Close
    $cnnTemp = ""
    $accApp.CloseCurrentDatabase
    $accApp = ""
EndFunc   ;==>ExportTableToOneExcel

;This is Sven P's custom error handler
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

Enjoy !!

ptrex

Edited February 6, 2008 by ptrex

itayzoro · February 7, 2008

HI PTrex 10nx 4 the script verry hlpful but CAN U PLZ FIX 1 bug :

ID TIME_START TIME_END DEVICE IN OUT DEVICE_SUB1

1 2/5/2008 11:53:18 AM 2/5/2008 11:58:18 AM 3 8 0 0

2 2/5/2008 11:58:18 AM 2/5/2008 12:03:18 PM 3 2 0 0

3 2/5/2008 12:03:18 PM 2/5/2008 12:08:18 PM 3 1 0 0

4 2/5/2008 12:08:18 PM 2/5/2008 12:08:31 PM 3 0 0 0

5 2/5/2008 12:09:52 PM 2/5/2008 12:14:52 PM 3 1 0 0

6 2/5/2008 12:14:52 PM 2/5/2008 12:19:52 PM 3 2 0 0

7 2/5/2008 12:19:52 PM 2/5/2008 12:24:52 PM 3 0 0 0

8 2/5/2008 12:24:52 PM 2/5/2008 12:29:52 PM 3 1 0 0

9 2/5/2008 12:29:52 PM 2/5/2008 12:34:52 PM 3 0 0 0

u c the Field "TIME_START" or "TIME_END" it contain Date+Time but and this is the big BUT when i export with your script no Time

mybe its just MYbe the "Space" but can u plz FIX it

Its the same ProblamE from the Command-line Exe : DbTkXml http://www.profsoftware.com/dbtkxml/

ptrex · February 7, 2008

@itayzoro

Date Formats Exported from Access to Excel

Access will / can not format the export information. It is possible to write Excel VBA code to format the cells in the target spreasheet. It is possible to write code in Ms. Access VBA to write the Excel VBA code to format the cells and to have the code exported tot he spreasheet. It is also possible to open the spreadsheet where the information was exported and format the cells from within Ms. Access using Ms. Access VBA code.
But you cannot get Ms. Access to format the information prior to / during the export process itself.

Solutions 1 :

If you export to an already formated but empty sheet of an excel file, all formats are kept on the sheet (except for column sizes autofit) as long as sheet name matches exported table/query name.

Solution 2 :

PULL the data from Access to Excel using MS Query.
Format as you wish, set the Data Range Properties to maintain formats (will adjust to the size of the querytable)
Then Copy that format to each other sheet, assuming the same format logic, and Edit the query as needed.

Regards

ptrex

itayzoro · February 7, 2008

Ok verry nice So in 1 LINE : MDB 2 XLS is not what im looking 4 so ...

I need 2 export 2 whatever format u want TXT HTML ... i just need 1 Table with all the fields whats so diffc.??!!

all the GUI prog do thats if u know about Command-linE prog that do this its ok 2

And if u can Slove this on script its better

what i need in the End is TXT . MDB --> Excel --> Txt its OK MDB --> Whatever --> Txt

the problam is I CANT WORK WITH MDB OR INSIDE MDB AND I NEED ONLY 1 TABLE + ALL THE INFO 1:1

2 weeks pass ... there always a SoLuTiOn

Edited February 7, 2008 by itayzoro

Will66 · February 7, 2008

Not sure exactly what your requiring, but maybe try this one: Here

ResNullius · February 7, 2008

Ok verry nice So in 1 LINE : MDB 2 XLS is not what im looking 4 so ...
I need 2 export 2 whatever format u want TXT HTML ... i just need 1 Table with all the fields whats so diffc.??!!

OK, so you have 5 posts, all in this thread, and your tone is beginning to sound like this is a "free on-demand solutions-to-all-your-coding-problems" forum.

ptrex has given you some very good code as a starting point and you respond like he doesn't understand what you want.

Have you put any effort into taking what's been given and trying to adapt it?

If so, show us your code and the fact that you're at least trying to do something on your own and you'd be surprised at how much more helpful people can be.

itayzoro · February 7, 2008

Hi ResNullIst U CAN STUCK YOUR nose IN THE ASS- I DONT ASK U 4 HLP SO .. instead of hlp u SAid :free on-demand solutions-to-all-your-coding-problems fu--ck u man u full of bullshit

And i am verry estimate PTrex and the Way he lead me my script is Done and Work

What u r Forum Cop that count how much post i Write or u just want 2 said : im not NOOB i got 3 X (Joined: 8-April 07)

So i can lol on the New guys who wants hlp !!!

PPL like u r bad 4 this Forum go away THIS IS HLP FORUM SO I CAN ASK 4 HLP WHAT EVER I WANT go and open :

a arrogance Forum 4 stupid like u

itayzoro · February 7, 2008

BTW THE SOLUTION IS BASE ON THIS SCRIPT AND USE OLEDB :

; Modify the variables below as desired or applicable

; ******************************************************

$dbname = "c:\1\tt.mdb"

$tblname = "DATA_COUNTER"

;$fldname = "txt1"

;$format = "Text(50)"

;$data = "Blah Blah Blah"

;$fldname = "num1"

;$format = "Number"

;$data = 99

$fldname = "TIME_END"

$format = "Date"

$data = @MON & "/" & @MDAY & "/" & StringRight(@YEAR, 2)

; Un-comment the action you want to do

; ******************************************************

;_CreateDB($dbname)

;_CreateTBL($dbname, $tblname)

;_CreateFLD($dbname, $tblname, $fldname, $format)

; Example of how to add data to a single field of a table

; ******************************************************

;_AddData($dbname, $tblname, $fldname, $data)

; Example of how to list all of the Fields in a particular table

; ******************************************************

;$str = _FieldNames($dbname, $tblname)

;MsgBox(0, "Fields in " & $tblname, $str)

; Example of how to read the info from all records in one field of a table as a string

; ******************************************************

;$query = "SELECT * FROM " & $tblname

;$strData = _ReadOneFld($query, $dbname, $fldname)

;MsgBox(0, $fldname, $strData)

; Example of how to get entire rows of pattern matched data

; This returns a two dimensional array where [0][0] holds the number of matching

; records found, [0][1] holds the first field name [0][2] holds the second etc.

; [1][1] is the data from the first record - first field

; [2][3] is the data from the 2nd record - 3rd field

; ******************************************************

;$query = "SELECT * FROM " & $tblname & " WHERE " & $fldname & " = 99"

;$rows = _RecordSearch($dbname, $query)

;_2dArrayDisp($rows)

; Example of how to add an entire record into a table at one time

; For this example:

; First make sure you have a database created including a table

; and also that the table has the fields that you want to use.

; Then create a 2 dimensional array populated much like the one above

; ******************************************************

;Dim $row[2][4]

;$row[0][0] = UBound($row, 1) - 1

;$row[0][1] = "num1"

;$row[1][1] = 251

;$row[0][2] = "txt1"; match the field name

;$row[1][2] = "Denver, CO"; with the data you are going to use

;$row[0][3] = "date1"

;$row[1][3] = @MON & "/" & @MDAY & "/" & StringRight(@YEAR, 2)

;_AddEntireRecord($dbname, $tblname, $row)

; this will show the entire table

;$query = "SELECT * FROM " & $tblname

;$rows = _RecordSearch($dbname, $query)

;_2dArrayDisp($rows)

Func _CreateDB($dbname)

$newMdb = ObjCreate ("ADOX.Catalog")

$newMdb.Create ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)

$newmdb.ActiveConnection.Close

EndFunc ;==>_CreateDB

Func _CreateTBL($dbname, $tblname)

$addtbl = ObjCreate ("ADODB.Connection")

$addTbl.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)

$addTbl.Execute ("CREATE TABLE " & $tblname)

$addtbl.Close

EndFunc ;==>_CreateTBL

Func _CreateFLD($dbname, $tblname, $fldname, $format)

$addfld = ObjCreate ("ADODB.Connection")

$addfld.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)

$addfld.Execute ("ALTER TABLE " & $tblname & " ADD " & $fldname & " " & $format)

$addfld.Close

EndFunc ;==>_CreateFLD

Func _AddData($dbname, $tblname, $fldname, $value)

$adoCon = ObjCreate ("ADODB.Connection")

$adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname & ";")

$adoRs = ObjCreate ("ADODB.Recordset")

$adoRs.CursorType = 2

$adoRs.LockType = 3

$adoRs.Open ("SELECT * FROM " & $tblname, $adoCon)

$adoRs.AddNew

$adoRs.Fields ($fldname).Value = $value

$adoRs.Update

$adoRs.Close

$adoCon.Close

EndFunc ;==>_AddData

Func _FieldNames($dbname, $tblname)

$adoCon = ObjCreate ("ADODB.Connection")

$adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname)

$adoRs = ObjCreate ("ADODB.Recordset")

$adoRs.Open ("SELECT * FROM " & $tblname, $adoCon)

$name = ""

For $i = 1 To $adoRs.Fields.Count

$name = $name & $adoRs.Fields ($i - 1).Name & @CRLF

EndFunc ;==>_FieldNames

Func _ReadOneFld($_sql, $_dbname, $_field)

Dim $_output

$adoCon = ObjCreate ("ADODB.Connection")

$adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname)

$adoRs = ObjCreate ("ADODB.Recordset")

$adoRs.CursorType = 1

$adoRs.LockType = 3

$adoRs.Open ($_sql, $adoCon)

With $adoRs

If .RecordCount Then

While Not .EOF

$_output = $_output & .Fields ($_field).Value & @CRLF

.MoveNext

WEnd

EndIf

EndWith

$adoCon.Close

Return $_output

EndFunc ;==>_ReadOneFld

Func _RecordSearch($_dbname, $_query)

$adoCon = ObjCreate ("ADODB.Connection")

$adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname)

$adoRs = ObjCreate ("ADODB.Recordset")

$adoRs.CursorType = 1

$adoRs.LockType = 3

$adoRs.Open ($_query, $adoCon)

With $adoRs

Dim $_output[.Fields.Count + 1][.RecordCount + 1]

$_output[0][0] = .RecordCount

For $i = 1 To .Fields.Count

$_output[$i][0] = .Fields ($i - 1).Name

$z = 0

While Not .EOF

$z = $z + 1

For $x = 1 To .Fields.Count

$_output[$x][$z] = .Fields ($x - 1).Value

WEnd

EndIf

EndWith

$adoCon.Close

Return $_output

EndFunc ;==>_RecordSearch

Func _AddEntireRecord($_dbname, $_tblname, $_array)

$adoCon = ObjCreate ("ADODB.Connection")

$adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $dbname & ";")

$adoRs = ObjCreate ("ADODB.Recordset")

$adoRs.CursorType = 2

$adoRs.LockType = 3

$adoRs.Open ("SELECT * FROM " & $tblname, $adoCon)

$adoRs.AddNew

$records = $_array[0][0]

For $i = 1 To $records

For $x = 1 To UBound($_array,2) - 1

$adoRs.Fields ($_array[0][$x]).Value = $_array[1][$x]

$adoRs.Update

$adoRs.Close

$adoCon.Close

EndFunc ;==>_AddEntireRecord

Func _2dArrayDisp($array)

$report = "Number of records matching = " & $array[0][0] & @CRLF

For $x = 1 To UBound($array, 2) - 1

For $i = 1 To UBound($array, 1) - 1

$report = $report & $array[$i][$x] & "|"

EndFunc ;==>_2dArrayDisp

;$str = _FieldNames($dbname, $tblname)

;MsgBox(0, "Fields in " & $tblname, $str)

$query = "SELECT * FROM " & $tblname

$strData = _ReadOneFld($query, $dbname, $fldname)

MsgBox(0, $fldname, $strData)

;$query = "SELECT * FROM " & $tblname

;$rows = _RecordSearch($dbname, $query)

;_2dArrayDisp($rows)

SORRY THAT I DONT KNOW WHO WRITE IT I JUST GOOGLE ON THE SITE SO I TEST A LOT OF SCRIPT UNTIL I UNDERSTAND WHAT I DO

ResNullius · February 7, 2008

SORRY THAT I DONT KNOW WHO WRITE IT I JUST GOOGLE ON THE SITE SO I TEST A LOT OF SCRIPT UNTIL I UNDERSTAND WHAT I DO

No need to apologize for that. By the way credit goes to SpookMeister for the code as you've listed it: http://www.autoitscript.com/forum/index.ph...st&p=168628

All I was trying to say was if more people would spend the time trying to find the answer themselves (like you obviously ended up doing) and then post any code they're having trouble with, it's alot better than apparently "demanding" solutions.

Sorry if I misinterpreted your post in reply to ptrex that I quoted.

But I know I didn't misinterpret your reply to me...

Edited February 7, 2008 by ResNullius

GaryFrost · February 8, 2008

Hi ResNullIst U CAN STUCK YOUR nose IN THE ASS- I DONT ASK U 4 HLP SO .. instead of hlp u SAid :free on-demand solutions-to-all-your-coding-problems fu--ck u man u full of bullshit
And i am verry estimate PTrex and the Way he lead me my script is Done and Work
What u r Forum Cop that count how much post i Write or u just want 2 said : im not NOOB i got 3 X (Joined: 8-April 07)
So i can lol on the New guys who wants hlp !!!
PPL like u r bad 4 this Forum go away THIS IS HLP FORUM SO I CAN ASK 4 HLP WHAT EVER I WANT go and open :
a arrogance Forum 4 stupid like u

No, but I am and you need to tone it down.

Swift · February 8, 2008

Is there a reason your abbrievating every word? It's hard to understand you when you type like this:

"I am nub dnt tlk to me u r nub so u cen stck up ur asr"

Really? how bad does that look? An english person missing 1-2 letters in each word...

Once again...

Posted Image

If this thread keeps going the ways its going...its going to get a huge *Click* and maybe a post into the 'Banned Users' Thread. Hope its fun!

Edited February 8, 2008 by Swift

GaryFrost · February 8, 2008

Is there a reason your abbrievating every word? It's hard to understand you when you type like this:
"I am nub dnt tlk to me u r nub so u cen stck up ur asr"
Really? how bad does that look? An english person missing 1-2 letters in each word...
Once again...
If this thread keeps going the ways its going...its going to get a huge *Click* and maybe a post into the 'Banned Users' Thread. Hope its fun!

Do yourself a favor and stay out of this.

Swift · February 8, 2008

Sorry Mr. Frost. I Shall Not Say Anything Else.

itayzoro · February 10, 2008

Hi Guyz u want start War on your Forum just say so i can Shutdown your forum go to torrentleech.org

and ask them whats hpnd to their Servers last month when they close my account (the name is itayzoro)

So GrayFrost shut up b4 it be 2 late last warning if i c 1 Replay that make me angry u will pay and all the Forum

and SWift i can spk whatever i want IM not english im ARAB and my english is verry poor

The Fast and easy way 2 dump Mdb table to TXT

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Recently Browsing 0 members