Assigning an Object to a variable

M

MAC

I have a table that contains a field 'rpt' with a data
type of OLE Object. I am trying to use VBA to assign it a
text file. I have also tried changing the data type of
the field to hyperlink but have run across the same
problems. Here is an example of my code

Dim stField1 as String
Dim intField2 as Integer
Dim stFileName as String
Dim obRpt as Object
Dim stDocName as String
Dim Conn as ADODB.Connection
Dim RS as New ADODB.Recordset
Dim DB as Database
Dim InFile as Integer
Dim InRec as String

InFile=FreeFile
Open \\ServerName\Archive\Dir.txt" as Input as #InFile
Input #InFile, InRec

stField1=left(InRec, 10)
intField2=CINT(mid(InRec, 11, 3))
stFileName=Trim(right(InRec,10))

Set DB=CurrentDB
Set Conn as New ADODB.Connection

With Conn
.Provider="Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source= " & DB.Name
.Open
End With

Set RS=New ADODB.Recordset
With RS
.CursorType=adOpenDynamic
.LockType=adLockOptimistic
.Open "Status_Rpts", Conn, , , adCmdTable
End With

stDocName= "\\ServerName\Archive\" & stFileName

'>>>This is where I have made several attempts at
assigning an Object to a variable
'>>>I Tried a direct assignment
obRpt=StDocName
'>>>I also tried
Set objRpt = GetObject(stDocName)
objRpt.SourceDoc = stDocName
objRpt.Action = acOLECreateEmbed
'>>>Nothing has worked.

With RS
.AddNew
![Field1]=strField1
![Field2]=intField2
![Rpt]=obRpt
.Update
End With

Set RS=Nothing
Set DB=Nothing
Conn.Close

While the Specs. Say that the Report should be in the DB,
I think they would be content with a link. I have been
working on this problem for some time and would appreciate
any help. Thanks


Michael Carrillo
 
J

John Nurick

Hi Mac,

If you want to store the contents of a text file, use a Memo field, read
the file from disk into a variable (see function below) and store the
string in the Memo field.

If you want to store the text file as an OLE object you need to package
it. Probalby the simplest way is to use a form with an objectframe
control bound to the OLE field. Have your code embed the file in the
field by manipulating the objectframe control (the field itself is not
"smart"); IIRC you'll need to use the SourceDocument and perhaps the
Verb and Action properties.

Often it's best just to store the name and path of the document, either
in a hypertext field or in a Text field. But if you are required to
actually store the document inside the database you may not be able to
get away with this.

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 a table that contains a field 'rpt' with a data
type of OLE Object. I am trying to use VBA to assign it a
text file. I have also tried changing the data type of
the field to hyperlink but have run across the same
problems. Here is an example of my code

Dim stField1 as String
Dim intField2 as Integer
Dim stFileName as String
Dim obRpt as Object
Dim stDocName as String
Dim Conn as ADODB.Connection
Dim RS as New ADODB.Recordset
Dim DB as Database
Dim InFile as Integer
Dim InRec as String

InFile=FreeFile
Open \\ServerName\Archive\Dir.txt" as Input as #InFile
Input #InFile, InRec

stField1=left(InRec, 10)
intField2=CINT(mid(InRec, 11, 3))
stFileName=Trim(right(InRec,10))

Set DB=CurrentDB
Set Conn as New ADODB.Connection

With Conn
.Provider="Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source= " & DB.Name
.Open
End With

Set RS=New ADODB.Recordset
With RS
.CursorType=adOpenDynamic
.LockType=adLockOptimistic
.Open "Status_Rpts", Conn, , , adCmdTable
End With

stDocName= "\\ServerName\Archive\" & stFileName

'>>>This is where I have made several attempts at
assigning an Object to a variable
'>>>I Tried a direct assignment
obRpt=StDocName
'>>>I also tried
Set objRpt = GetObject(stDocName)
objRpt.SourceDoc = stDocName
objRpt.Action = acOLECreateEmbed
'>>>Nothing has worked.

With RS
.AddNew
![Field1]=strField1
![Field2]=intField2
![Rpt]=obRpt
.Update
End With

Set RS=Nothing
Set DB=Nothing
Conn.Close

While the Specs. Say that the Report should be in the DB,
I think they would be content with a link. I have been
working on this problem for some time and would appreciate
any help. Thanks


Michael Carrillo
Click to expand...
 
M

MAC

Cool! It hadn't occured to me to use memo data type. I
will definately try this. However, I would also like to
try hyperlink. Can you suggest how best to assign a
variable with a path to a hyperlink?

Thanks!

Michael
-----Original Message-----
Hi Mac,

If you want to store the contents of a text file, use a Memo field, read
the file from disk into a variable (see function below) and store the
string in the Memo field.

If you want to store the text file as an OLE object you need to package
it. Probalby the simplest way is to use a form with an objectframe
control bound to the OLE field. Have your code embed the file in the
field by manipulating the objectframe control (the field itself is not
"smart"); IIRC you'll need to use the SourceDocument and perhaps the
Verb and Action properties.

Often it's best just to store the name and path of the document, either
in a hypertext field or in a Text field. But if you are required to
actually store the document inside the database you may not be able to
get away with this.

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 a table that contains a field 'rpt' with a data
type of OLE Object. I am trying to use VBA to assign it a
text file. I have also tried changing the data type of
the field to hyperlink but have run across the same
problems. Here is an example of my code

Dim stField1 as String
Dim intField2 as Integer
Dim stFileName as String
Dim obRpt as Object
Dim stDocName as String
Dim Conn as ADODB.Connection
Dim RS as New ADODB.Recordset
Dim DB as Database
Dim InFile as Integer
Dim InRec as String

InFile=FreeFile
Open \\ServerName\Archive\Dir.txt" as Input as #InFile
Input #InFile, InRec

stField1=left(InRec, 10)
intField2=CINT(mid(InRec, 11, 3))
stFileName=Trim(right(InRec,10))

Set DB=CurrentDB
Set Conn as New ADODB.Connection

With Conn
.Provider="Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source= " & DB.Name
.Open
End With

Set RS=New ADODB.Recordset
With RS
.CursorType=adOpenDynamic
.LockType=adLockOptimistic
.Open "Status_Rpts", Conn, , , adCmdTable
End With

stDocName= "\\ServerName\Archive\" & stFileName

'>>>This is where I have made several attempts at
assigning an Object to a variable
'>>>I Tried a direct assignment
obRpt=StDocName
'>>>I also tried
Set objRpt = GetObject(stDocName)
objRpt.SourceDoc = stDocName
objRpt.Action = acOLECreateEmbed
'>>>Nothing has worked.

With RS
.AddNew
![Field1]=strField1
![Field2]=intField2
![Rpt]=obRpt
.Update
End With

Set RS=Nothing
Set DB=Nothing
Conn.Close

While the Specs. Say that the Report should be in the DB,
I think they would be content with a link. I have been
working on this problem for some time and would appreciate
any help. Thanks


Michael Carrillo
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
J

John Nurick

Hi Michael,

Ummm. I'm not quite sure what you're after. Assuming you've opened a
recordset RS on your table, perhaps something like this:

...
Dim strFileSpec As String
...

RS.AddNew
strFileSpec = "\\Servername\Share\Folder\Document.txt"
RS.Fields("LinkedFile").Value = strFileSpec
'assign values to other fields if needed
...
RS.Update



Cool! It hadn't occured to me to use memo data type. I
will definately try this. However, I would also like to
try hyperlink. Can you suggest how best to assign a
variable with a path to a hyperlink?

Thanks!

Michael
-----Original Message-----
Hi Mac,

If you want to store the contents of a text file, use a Memo field, read
the file from disk into a variable (see function below) and store the
string in the Memo field.

If you want to store the text file as an OLE object you need to package
it. Probalby the simplest way is to use a form with an objectframe
control bound to the OLE field. Have your code embed the file in the
field by manipulating the objectframe control (the field itself is not
"smart"); IIRC you'll need to use the SourceDocument and perhaps the
Verb and Action properties.

Often it's best just to store the name and path of the document, either
in a hypertext field or in a Text field. But if you are required to
actually store the document inside the database you may not be able to
get away with this.

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 a table that contains a field 'rpt' with a data
type of OLE Object. I am trying to use VBA to assign it a
text file. I have also tried changing the data type of
the field to hyperlink but have run across the same
problems. Here is an example of my code

Dim stField1 as String
Dim intField2 as Integer
Dim stFileName as String
Dim obRpt as Object
Dim stDocName as String
Dim Conn as ADODB.Connection
Dim RS as New ADODB.Recordset
Dim DB as Database
Dim InFile as Integer
Dim InRec as String

InFile=FreeFile
Open \\ServerName\Archive\Dir.txt" as Input as #InFile
Input #InFile, InRec

stField1=left(InRec, 10)
intField2=CINT(mid(InRec, 11, 3))
stFileName=Trim(right(InRec,10))

Set DB=CurrentDB
Set Conn as New ADODB.Connection

With Conn
.Provider="Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source= " & DB.Name
.Open
End With

Set RS=New ADODB.Recordset
With RS
.CursorType=adOpenDynamic
.LockType=adLockOptimistic
.Open "Status_Rpts", Conn, , , adCmdTable
End With

stDocName= "\\ServerName\Archive\" & stFileName

'>>>This is where I have made several attempts at
assigning an Object to a variable
'>>>I Tried a direct assignment
obRpt=StDocName
'>>>I also tried
Set objRpt = GetObject(stDocName)
objRpt.SourceDoc = stDocName
objRpt.Action = acOLECreateEmbed
'>>>Nothing has worked.

With RS
.AddNew
![Field1]=strField1
![Field2]=intField2
![Rpt]=obRpt
.Update
End With

Set RS=Nothing
Set DB=Nothing
Conn.Close

While the Specs. Say that the Report should be in the DB,
I think they would be content with a link. I have been
working on this problem for some time and would appreciate
any help. Thanks


Michael Carrillo
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
M

MAC

Many Thanks! Both worked out Great!
The only modification I made was to your code below:

dim stDocName as string
dim strFileSpec as string
....
RS.AddNew
strFileSpec = "\\Servername\Share\Folder\Document.txt"
stDocName= strFileSpec & "#" & strFileSpec
RS.Fields("LinkedFile").Value = stDocName
....
RS.Update

This allows me to set both the Displayed Link Name and the
Link itself. Without the '#' I was only assigning the
Displayed Link Name.

I do like giving my Users a Choice. Thanks Again!

Michael
-----Original Message-----
Hi Michael,

Ummm. I'm not quite sure what you're after. Assuming you've opened a
recordset RS on your table, perhaps something like this:

...
Dim strFileSpec As String
...

RS.AddNew
strFileSpec
Click to expand...
= "\\Servername\Share\Folder\Document.txt"
RS.Fields("LinkedFile").Value = strFileSpec
'assign values to other fields if needed
...
RS.Update



Cool! It hadn't occured to me to use memo data type. I
will definately try this. However, I would also like to
try hyperlink. Can you suggest how best to assign a
variable with a path to a hyperlink?

Thanks!

Michael
-----Original Message-----
Hi Mac,

If you want to store the contents of a text file, use a Memo field, read
the file from disk into a variable (see function below) and store the
string in the Memo field.

If you want to store the text file as an OLE object you need to package
it. Probalby the simplest way is to use a form with an objectframe
control bound to the OLE field. Have your code embed
Click to expand...
the
file in the
field by manipulating the objectframe control (the
Click to expand...
field
itself is not
"smart"); IIRC you'll need to use the SourceDocument
Click to expand...
and
perhaps the
Verb and Action properties.

Often it's best just to store the name and path of the document, either
in a hypertext field or in a Text field. But if you are required to
actually store the document inside the database you may not be able to
get away with this.

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 Thu, 5 Feb 2004 11:12:27 -0800, "MAC"

I have a table that contains a field 'rpt' with a data
type of OLE Object. I am trying to use VBA to assign
Click to expand...
it
a
text file. I have also tried changing the data type of
the field to hyperlink but have run across the same
problems. Here is an example of my code

Dim stField1 as String
Dim intField2 as Integer
Dim stFileName as String
Dim obRpt as Object
Dim stDocName as String
Dim Conn as ADODB.Connection
Dim RS as New ADODB.Recordset
Dim DB as Database
Dim InFile as Integer
Dim InRec as String

InFile=FreeFile
Open \\ServerName\Archive\Dir.txt" as Input as #InFile
Input #InFile, InRec

stField1=left(InRec, 10)
intField2=CINT(mid(InRec, 11, 3))
stFileName=Trim(right(InRec,10))

Set DB=CurrentDB
Set Conn as New ADODB.Connection

With Conn
.Provider="Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source= " & DB.Name
.Open
End With

Set RS=New ADODB.Recordset
With RS
.CursorType=adOpenDynamic
.LockType=adLockOptimistic
.Open "Status_Rpts", Conn, , , adCmdTable
End With

stDocName= "\\ServerName\Archive\" & stFileName

'>>>This is where I have made several attempts at
assigning an Object to a variable
'>>>I Tried a direct assignment
obRpt=StDocName
'>>>I also tried
Set objRpt = GetObject(stDocName)
objRpt.SourceDoc = stDocName
objRpt.Action = acOLECreateEmbed
'>>>Nothing has worked.

With RS
.AddNew
![Field1]=strField1
![Field2]=intField2
![Rpt]=obRpt
.Update
End With

Set RS=Nothing
Set DB=Nothing
Conn.Close

While the Specs. Say that the Report should be in the DB,
I think they would be content with a link. I have been
working on this problem for some time and would appreciate
any help. Thanks


Michael Carrillo

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Oracle Connection String 1
SQL syntax 1
Can't get this outputto loop to work 4
the request for procedure 'y' failed because 'y' is a table valued function object 1
ADD New Question 4
Access VBA to format Excel 1
importing a worksheet (excel) 1
How to Open a Password encrypted .accdb 0

Top