By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,471 Members | 2,264 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,471 IT Pros & Developers. It's quick & easy.

Default Import Specification (for TransferText etc)

P: n/a
MIG
I am aware that when importing a file it's possible to save a
specification that says, for example, treat a particular field as
text, whenever importing a file of exactly the same format.

It's also possible to use the TransferText method with such a
specification as an argument.

However, it's a complete pain having to save a different specification
for every new file format.

It is infuriating that, without a specification, Access assumes that a
field which has an alphabetical character in the second record is
nevertheless an integer, and then fails to import half of the data.

Is there a way of telling Access to assume by default that EVERY field
is text, so save me having to save one specification after another,
all of which do the same thing?

Another question for when more users are involved: are the
specifications saved in the database or in the individual copies of
Access, requiring every user to save the same list of speficications?

Jul 23 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
The specification is saved in the mdb where it was created. If you
create a new spec on an mdb and each user has a separate copy of the mdb
- you need to distribute that mdb so that everyone has an updated
version of it.

But instead of using a specificate, if you have to distribute the
application - it is much easier to use VBA code to write/read data
to/from textfiles. No specifications involved. Here is a sample:

---------------------------------------------
Sub writeDataToTextFile()
Dim DB As DAO.Database, RS As DAO.Recordset
Dim str1 As String, strPath As String, i As Integer
Set DB = CurrentDb
Set RS = DB.OpenRecordset("tbl1")
strPath = CurrentProject.Path
Debug.Print strPath
Close #1
Open strPath & "\testFile.txt" For Append As #1
Do While Not RS.EOF
str1 = ""
For i = 0 To RS.Fields.Count - 2
str1 = str1 & RS(i) & ","
Next
str1 = str1 & RS(i)
Print #1, str1 '--using print to output to a text file does not
add double quotes like the Write method
RS.MoveNext
Loop
Close #1
RS.MoveFirst
Open strPath & "\testFile2.txt" For Append As #1
Do While Not RS.EOF
str1 = ""
For i = 0 To RS.Fields.Count - 2
str1 = str1 & RS(i) & ","
Next
str1 = str1 & RS(i)
Write #1, str1 '--using Write surrounds the text in double quotes
" "
RS.MoveNext
Loop
RS.Close
Close #1

End Sub
-----------------------------------------------

The Access help files have more information on the "Open" statement,
"Append", "Close", "Line", "Input", "Split"
'--and here is sample code for reading from a text file
-------------------------------------------------
Sub ReadText()
Dim str1 As String, str2() As String
Dim i As Integer, j As Integer, k As Integer
Open "c:\somepath\testfile.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, str1
str2 = Split(str1, vbTab)
For i = 0 To UBound(str2)
Debug.Print str2(i) & " ";
Next
Debug.Print
Loop
Close #1

End Sub
--------------------------------------------------

for reading text from textfiles, I use the Line method which reads each
line of text (delimited), and then I use the Split function to place
each datafield into an array -- str2(). You must have a table with the
same number of fields as the data you are reading in. You can loop
through the data in the text file using a Do While Loop, and then loop
through the Split array using a For loop as in the example above. Note:
using a semicolon at the end of the debug statement places the data in
the For loop on the same line. Without the ; each time you debug.print
something from str2(i) -- it would print on a new line in the debug
window.

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jul 23 '07 #2

P: n/a
MIG
On Jul 23, 7:05 pm, Rich P <rpng...@aol.comwrote:
The specification is saved in the mdb where it was created. If you
create a new spec on an mdb and each user has a separate copy of the mdb
- you need to distribute that mdb so that everyone has an updated
version of it.

Ah; that's a Good Thing. Much easier than trying to change everyone's
local settings.

>
But instead of using a specificate, if you have to distribute the
application - it is much easier to use VBA code to write/read data
to/from textfiles. No specifications involved. Here is a sample:

Thanks. I will have a go at following it ...
>
---------------------------------------------
Sub writeDataToTextFile()
Dim DB As DAO.Database, RS As DAO.Recordset
Dim str1 As String, strPath As String, i As Integer
Set DB = CurrentDb
Set RS = DB.OpenRecordset("tbl1")
strPath = CurrentProject.Path
Debug.Print strPath
Close #1
Open strPath & "\testFile.txt" For Append As #1
Do While Not RS.EOF
str1 = ""
For i = 0 To RS.Fields.Count - 2
str1 = str1 & RS(i) & ","
Next
str1 = str1 & RS(i)
Print #1, str1 '--using print to output to a text file does not
add double quotes like the Write method
RS.MoveNext
Loop
Close #1
RS.MoveFirst
Open strPath & "\testFile2.txt" For Append As #1
Do While Not RS.EOF
str1 = ""
For i = 0 To RS.Fields.Count - 2
str1 = str1 & RS(i) & ","
Next
str1 = str1 & RS(i)
Write #1, str1 '--using Write surrounds the text in double quotes
" "
RS.MoveNext
Loop
RS.Close
Close #1

End Sub
-----------------------------------------------

The Access help files have more information on the "Open" statement,
"Append", "Close", "Line", "Input", "Split"

'--and here is sample code for reading from a text file
-------------------------------------------------
Sub ReadText()
Dim str1 As String, str2() As String
Dim i As Integer, j As Integer, k As Integer
Open "c:\somepath\testfile.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, str1
str2 = Split(str1, vbTab)
For i = 0 To UBound(str2)
Debug.Print str2(i) & " ";
Next
Debug.Print
Loop
Close #1

End Sub
--------------------------------------------------

for reading text from textfiles, I use the Line method which reads each
line of text (delimited), and then I use the Split function to place
each datafield into an array -- str2(). You must have a table with the
same number of fields as the data you are reading in. You can loop
through the data in the text file using a Do While Loop, and then loop
through the Split array using a For loop as in the example above. Note:
using a semicolon at the end of the debug statement places the data in
the For loop on the same line. Without the ; each time you debug.print
something from str2(i) -- it would print on a new line in the debug
window.

HTH

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Jul 23 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.