MS SQL

Access table data transfer to file

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Sep 2006

Posts: 20

Reputation: saurabh_kanwar is an unknown quantity at this point

Solved Threads: 0

saurabh_kanwar saurabh_kanwar is offline

Offline

Newbie Poster

Access table data transfer to file

Nov 28th, 2006

Hi Guys,

I was just trying to transfer data from access tables to a txt file and then back from file into a table using following code

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
 
Private Sub Command0_Click() 
DoCmd.TransferText acExportDelim, "Db Export Specification", "AUTHENTICATION", "J:\Power Equipment\Common\SK\db.TXT" 
End Sub 
 
Private Sub Command1_Click() 
strSQL = "delete * from AUTHENTICATION_TEST" 
DoCmd.RunSQL strSQL 
DoCmd.TransferText acImportDelim, "Db Import Authentication Specification", "AUTHENTICATION_TEST", "J:\Power Equipment\Common\SK\db.TXT" 
End Sub
 
Private Sub Command0_Click() 
DoCmd.TransferText acExportDelim, "Db Export Specification", "AUTHENTICATION", "J:\Power Equipment\Common\SK\db.TXT" 
End Sub 
 
Private Sub Command1_Click() 
strSQL = "delete * from AUTHENTICATION_TEST" 
DoCmd.RunSQL strSQL 
DoCmd.TransferText acImportDelim, "Db Import Authentication Specification", "AUTHENTICATION_TEST", "J:\Power Equipment\Common\SK\db.TXT" 
End Sub

Is it possible to create and manipulate import/export specifications using the VBA code rather than the access manu so that I can make it work on different systems.

Thanks for your time

•

Join Date: Oct 2006

Posts: 2

Reputation: TheDugglerrr is an unknown quantity at this point

Solved Threads: 0

TheDugglerrr TheDugglerrr is offline

Offline

Newbie Poster

Re: Access table data transfer to file

Nov 30th, 2006

Hi Saurabh,

It sounds to me like you require the ability to specify the Data-Type when importing fields into MSAccess, because sometimes MSAccess guesses the field types incorrectly.

I dont have the complete solution, but a suggested starting point.

The idea is to create a recordset (based on your SQL), and then read through it one record at a time. each record is then written to the textfile.

Theres a bit of code missing lol, this isnt a copy n paste solution my friend

Anyways, I hope I helped

good luck!

Private Sub Command0_Click()

'Create a Connection Object
'Create a Recordset object based on your SQL

Open "J:\Power Equipment\Common\SK\db.TXT" For Output As #1

While Not rst.EOF

Print #1, rst("ImageID") & vbTab & rst("ImageWidth" & vbCrLf)

Wend

Close #1

End Sub

Private Sub Command1_Click()
strSQL = "delete * from AUTHENTICATION_TEST"
DoCmd.RunSQL strSQL
DoCmd.TransferText acImportDelim, "Db Import Authentication Specification", "AUTHENTICATION_TEST", "J:\Power Equipment\Common\SK\db.TXT"
End Sub