Access XP: how do i get a text document into a memo field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have an Access XP database that contains a field with a text document name.
i need to add a memo field with that text document, keeping the format of the
document.

once i learn how to import this text document into my data field i will need
to create a macro or some automation for this process since i have over 34
thousand records i need to import the coresponding text documents

thanks
 
Hi Dwight,

The function below will read and return the contents of a textfile, and
can be used in an Access query. Paste it into a module in your database
(making sure the module is not named "FileContents").

Make a copy of your database to be on the safe side. Add a memo field to
the table - I'll call it fldMemo - and then create an update query on
the table to update fldMemo. The "Update To" cell will look something
like this:

FileContents("D:\Folder\" & [XXX])

replacing D:\Folder\ with the actual location of your files and XXX with
the name of the field that contains the filenames. If the field contains
the filespecs and not just the names all you need is
FileContents([XXX]).


Public Function FileContents(FileSpec As Variant, _
Optional ReturnErrors As Boolean = False, _
Optional ByRef ErrCode As Long) As Variant
'Retrieves contents of file as a string
'Silently returns Null on error unless
' ReturnErrors is true, in which case
' uses CVErr() to return an error value.
' Optionally, you can retrieve the error
' code in the ErrCode argument

Dim lngFN As Long

On Error GoTo Err_FileContents
If IsNull(FileSpec) Then
FileContents = Null
Else
lngFN = FreeFile()
Open FileSpec For Input As #lngFN
FileContents = Input(LOF(lngFN), #lngFN)
End If
ErrCode = 0
GoTo Exit_FileContents

Err_FileContents:
ErrCode = Err.Number
If ReturnErrors Then
FileContents = CVErr(Err.Number)
Else
FileContents = Null
End If
Err.Clear
Exit_FileContents:
Close #lngFN
End Function
 
Thanks John, I tried the update query and it ran the query for a long time
but when i look in the datasheet view of the table i don't see anything in my
new memo field. i wasn't sure what to do with the module information you
posted. please excuse me i'm sort of new using Access so i don't know that
much about using it.

How do i see that the text documents imported? or am i not doing this correct?

John Nurick said:
Hi Dwight,

The function below will read and return the contents of a textfile, and
can be used in an Access query. Paste it into a module in your database
(making sure the module is not named "FileContents").

Make a copy of your database to be on the safe side. Add a memo field to
the table - I'll call it fldMemo - and then create an update query on
the table to update fldMemo. The "Update To" cell will look something
like this:

FileContents("D:\Folder\" & [XXX])

replacing D:\Folder\ with the actual location of your files and XXX with
the name of the field that contains the filenames. If the field contains
the filespecs and not just the names all you need is
FileContents([XXX]).


Public Function FileContents(FileSpec As Variant, _
Optional ReturnErrors As Boolean = False, _
Optional ByRef ErrCode As Long) As Variant
'Retrieves contents of file as a string
'Silently returns Null on error unless
' ReturnErrors is true, in which case
' uses CVErr() to return an error value.
' Optionally, you can retrieve the error
' code in the ErrCode argument

Dim lngFN As Long

On Error GoTo Err_FileContents
If IsNull(FileSpec) Then
FileContents = Null
Else
lngFN = FreeFile()
Open FileSpec For Input As #lngFN
FileContents = Input(LOF(lngFN), #lngFN)
End If
ErrCode = 0
GoTo Exit_FileContents

Err_FileContents:
ErrCode = Err.Number
If ReturnErrors Then
FileContents = CVErr(Err.Number)
Else
FileContents = Null
End If
Err.Clear
Exit_FileContents:
Close #lngFN
End Function


i have an Access XP database that contains a field with a text document name.
i need to add a memo field with that text document, keeping the format of the
document.

once i learn how to import this text document into my data field i will need
to create a macro or some automation for this process since i have over 34
thousand records i need to import the coresponding text documents

thanks
Click to expand...
Click to expand...
 
Dwight,

Remember that the table datasheet view only shows you the first line of
a memo field. Try using a form with a textbox bound to the memo field so
you can scroll through the entire contents.

If that's not the problem, delete the existing FileContents code from
your database, create a new module (don't use a form's module), and
paste the FileContents() code into it.

Then, still in the VB editor, hit Ctrl-G to go to the Immediate pane.
Type
?FileContents("D:\Folder\Filename.txt")
(substituting the actual path and name of one or your textfiles). The
contents of the file should then appear in the Immediate pane.

If they do appear, the function is working properly. If not, make
certain that you are typing a correct filename. Once this is working,
try your query again. (To make it faster while testing, add a criterion
to make it only updates one or two records.)

If you're still having trouble, post back here with details of any error
messages you're getting and (b) the SQL view of your query, copied and
pasted from the query editor.

On Wed, 6 Oct 2004 14:35:23 -0700, "Dwight Huffman" <Dwight
Thanks John, I tried the update query and it ran the query for a long time
but when i look in the datasheet view of the table i don't see anything in my
new memo field. i wasn't sure what to do with the module information you
posted. please excuse me i'm sort of new using Access so i don't know that
much about using it.

How do i see that the text documents imported? or am i not doing this correct?

John Nurick said:
Hi Dwight,

The function below will read and return the contents of a textfile, and
can be used in an Access query. Paste it into a module in your database
(making sure the module is not named "FileContents").

Make a copy of your database to be on the safe side. Add a memo field to
the table - I'll call it fldMemo - and then create an update query on
the table to update fldMemo. The "Update To" cell will look something
like this:

FileContents("D:\Folder\" & [XXX])

replacing D:\Folder\ with the actual location of your files and XXX with
the name of the field that contains the filenames. If the field contains
the filespecs and not just the names all you need is
FileContents([XXX]).


Public Function FileContents(FileSpec As Variant, _
Optional ReturnErrors As Boolean = False, _
Optional ByRef ErrCode As Long) As Variant
'Retrieves contents of file as a string
'Silently returns Null on error unless
' ReturnErrors is true, in which case
' uses CVErr() to return an error value.
' Optionally, you can retrieve the error
' code in the ErrCode argument

Dim lngFN As Long

On Error GoTo Err_FileContents
If IsNull(FileSpec) Then
FileContents = Null
Else
lngFN = FreeFile()
Open FileSpec For Input As #lngFN
FileContents = Input(LOF(lngFN), #lngFN)
End If
ErrCode = 0
GoTo Exit_FileContents

Err_FileContents:
ErrCode = Err.Number
If ReturnErrors Then
FileContents = CVErr(Err.Number)
Else
FileContents = Null
End If
Err.Clear
Exit_FileContents:
Close #lngFN
End Function


i have an Access XP database that contains a field with a text document name.
i need to add a memo field with that text document, keeping the format of the
document.

once i learn how to import this text document into my data field i will need
to create a macro or some automation for this process since i have over 34
thousand records i need to import the coresponding text documents

thanks
Click to expand...
Click to expand...
Click to expand...
 
ok i checked for the text file in form view and there was nothing there
either. So i deleted the FileContents module then recreated it with the
Immediate view and when i typed in the ?FileContents... with a real file name
it came up Null. i opened that file in notepad to confirm that there was a
text document there.

Here is the copy of the update query you asked for.

FileContents("c:\sixmonth_data\text_files\" & [DocName])

i'm sorry i'm not understanding what's going on, but i really appreaciate
your help.

Thanks

Dwight
John Nurick said:
Hi Dwight,

The function below will read and return the contents of a textfile, and
can be used in an Access query. Paste it into a module in your database
(making sure the module is not named "FileContents").

Make a copy of your database to be on the safe side. Add a memo field to
the table - I'll call it fldMemo - and then create an update query on
the table to update fldMemo. The "Update To" cell will look something
like this:

FileContents("D:\Folder\" & [XXX])

replacing D:\Folder\ with the actual location of your files and XXX with
the name of the field that contains the filenames. If the field contains
the filespecs and not just the names all you need is
FileContents([XXX]).


Public Function FileContents(FileSpec As Variant, _
Optional ReturnErrors As Boolean = False, _
Optional ByRef ErrCode As Long) As Variant
'Retrieves contents of file as a string
'Silently returns Null on error unless
' ReturnErrors is true, in which case
' uses CVErr() to return an error value.
' Optionally, you can retrieve the error
' code in the ErrCode argument

Dim lngFN As Long

On Error GoTo Err_FileContents
If IsNull(FileSpec) Then
FileContents = Null
Else
lngFN = FreeFile()
Open FileSpec For Input As #lngFN
FileContents = Input(LOF(lngFN), #lngFN)
End If
ErrCode = 0
GoTo Exit_FileContents

Err_FileContents:
ErrCode = Err.Number
If ReturnErrors Then
FileContents = CVErr(Err.Number)
Else
FileContents = Null
End If
Err.Clear
Exit_FileContents:
Close #lngFN
End Function


i have an Access XP database that contains a field with a text document name.
i need to add a memo field with that text document, keeping the format of the
document.

once i learn how to import this text document into my data field i will need
to create a macro or some automation for this process since i have over 34
thousand records i need to import the coresponding text documents

thanks
Click to expand...
Click to expand...
 
ok i checked for the text file in form view and there was nothing there
either. So i deleted the FileContents module then recreated it with the
Immediate view and when i typed in the ?FileContents... with a real file name
it came up Null. i opened that file in notepad to confirm that there was a
text document there.
Click to expand...

If you type this into the Immediate pane, substituting the path to a
real textfile on your system. Remember that you must include the "txt"
extension:

? FileContents("D:\Folder\Other Folder\Filename.txt")

and the result is
Null
then this needs to be sorted out or there's no chance of the update
query working.

First, check that you've typed the path accurately. Copy it to the
clipboard - including the quote marks. Then go to the Windows Start
menu, select Run..., and paste the path into the Open: field and hit OK.

The file should open in Notepad or another text editor. If not, then
you've probably mistyped the path.


Having confirmed that you've got the file name and path correct, the
next thing is to check the way the FileContents() code is executing.

1 Back in the VB editor, set a breakpoint at the beginning of the
function by clicking in the left margin of the code pane next to
Public Function
2 Then open the Debug toolbar.
3 In the Immediate Pane, click on the
? FileContents("D:\Folder\Other Folder\Filename.txt")
and hit Enter.
4 Use the Step Into button on the Debug toolbar to step through the code
line by line, following the successively highlighted lines of code to
observe the execution path. If the highlight gets to this part of the
code
Err_FileContents:
ErrCode = Err.Number
If ReturnErrors Then
stop, go to the Immediate Pane, and type

? Err.Number, Err.Description
hit Enter, and post back here with the results.








Here is the copy of the update query you asked for.

FileContents("c:\sixmonth_data\text_files\" & [DocName])

i'm sorry i'm not understanding what's going on, but i really appreaciate
your help.

Thanks

Dwight
John Nurick said:
Hi Dwight,

The function below will read and return the contents of a textfile, and
can be used in an Access query. Paste it into a module in your database
(making sure the module is not named "FileContents").

Make a copy of your database to be on the safe side. Add a memo field to
the table - I'll call it fldMemo - and then create an update query on
the table to update fldMemo. The "Update To" cell will look something
like this:

FileContents("D:\Folder\" & [XXX])

replacing D:\Folder\ with the actual location of your files and XXX with
the name of the field that contains the filenames. If the field contains
the filespecs and not just the names all you need is
FileContents([XXX]).


Public Function FileContents(FileSpec As Variant, _
Optional ReturnErrors As Boolean = False, _
Optional ByRef ErrCode As Long) As Variant
'Retrieves contents of file as a string
'Silently returns Null on error unless
' ReturnErrors is true, in which case
' uses CVErr() to return an error value.
' Optionally, you can retrieve the error
' code in the ErrCode argument

Dim lngFN As Long

On Error GoTo Err_FileContents
If IsNull(FileSpec) Then
FileContents = Null
Else
lngFN = FreeFile()
Open FileSpec For Input As #lngFN
FileContents = Input(LOF(lngFN), #lngFN)
End If
ErrCode = 0
GoTo Exit_FileContents

Err_FileContents:
ErrCode = Err.Number
If ReturnErrors Then
FileContents = CVErr(Err.Number)
Else
FileContents = Null
End If
Err.Clear
Exit_FileContents:
Close #lngFN
End Function


i have an Access XP database that contains a field with a text document name.
i need to add a memo field with that text document, keeping the format of the
document.

once i learn how to import this text document into my data field i will need
to create a macro or some automation for this process since i have over 34
thousand records i need to import the coresponding text documents

thanks
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help, that got it.

Dwight

John Nurick said:
ok i checked for the text file in form view and there was nothing there
either. So i deleted the FileContents module then recreated it with the
Immediate view and when i typed in the ?FileContents... with a real file name
it came up Null. i opened that file in notepad to confirm that there was a
text document there.
Click to expand...

If you type this into the Immediate pane, substituting the path to a
real textfile on your system. Remember that you must include the "txt"
extension:

? FileContents("D:\Folder\Other Folder\Filename.txt")

and the result is
Null
then this needs to be sorted out or there's no chance of the update
query working.

First, check that you've typed the path accurately. Copy it to the
clipboard - including the quote marks. Then go to the Windows Start
menu, select Run..., and paste the path into the Open: field and hit OK.

The file should open in Notepad or another text editor. If not, then
you've probably mistyped the path.


Having confirmed that you've got the file name and path correct, the
next thing is to check the way the FileContents() code is executing.

1 Back in the VB editor, set a breakpoint at the beginning of the
function by clicking in the left margin of the code pane next to
Public Function
2 Then open the Debug toolbar.
3 In the Immediate Pane, click on the
? FileContents("D:\Folder\Other Folder\Filename.txt")
and hit Enter.
4 Use the Step Into button on the Debug toolbar to step through the code
line by line, following the successively highlighted lines of code to
observe the execution path. If the highlight gets to this part of the
code
Err_FileContents:
ErrCode = Err.Number
If ReturnErrors Then
stop, go to the Immediate Pane, and type

? Err.Number, Err.Description
hit Enter, and post back here with the results.








Here is the copy of the update query you asked for.

FileContents("c:\sixmonth_data\text_files\" & [DocName])

i'm sorry i'm not understanding what's going on, but i really appreaciate
your help.

Thanks

Dwight
John Nurick said:
Hi Dwight,

The function below will read and return the contents of a textfile, and
can be used in an Access query. Paste it into a module in your database
(making sure the module is not named "FileContents").

Make a copy of your database to be on the safe side. Add a memo field to
the table - I'll call it fldMemo - and then create an update query on
the table to update fldMemo. The "Update To" cell will look something
like this:

FileContents("D:\Folder\" & [XXX])

replacing D:\Folder\ with the actual location of your files and XXX with
the name of the field that contains the filenames. If the field contains
the filespecs and not just the names all you need is
FileContents([XXX]).


Public Function FileContents(FileSpec As Variant, _
Optional ReturnErrors As Boolean = False, _
Optional ByRef ErrCode As Long) As Variant
'Retrieves contents of file as a string
'Silently returns Null on error unless
' ReturnErrors is true, in which case
' uses CVErr() to return an error value.
' Optionally, you can retrieve the error
' code in the ErrCode argument

Dim lngFN As Long

On Error GoTo Err_FileContents
If IsNull(FileSpec) Then
FileContents = Null
Else
lngFN = FreeFile()
Open FileSpec For Input As #lngFN
FileContents = Input(LOF(lngFN), #lngFN)
End If
ErrCode = 0
GoTo Exit_FileContents

Err_FileContents:
ErrCode = Err.Number
If ReturnErrors Then
FileContents = CVErr(Err.Number)
Else
FileContents = Null
End If
Err.Clear
Exit_FileContents:
Close #lngFN
End Function


On Tue, 5 Oct 2004 12:39:04 -0700, Dwight

i have an Access XP database that contains a field with a text document name.
i need to add a memo field with that text document, keeping the format of the
document.

once i learn how to import this text document into my data field i will need
to create a macro or some automation for this process since i have over 34
thousand records i need to import the coresponding text documents

thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top