exporting table with OLE objects using VBA

[Gez]

Hi,

I've posted a number of requests on this one and got some great responses
but its not quite there yet.

Background:

I have a table containing field1 (picture description) field2 OLE objects
containing picture.
The table is dynamic in that it can be updated by a user with new images and
descriptions but I want to offer the flexibility of saving the table to disk
so that it can be import again at a later date.

The most promising way I've found is by using the export menu option and
performing the following:

Create a new .dbf file which you can name
Import the table back in.containing the OLE objects in the table

This proves that it can be done.

Obviously I don't want the user to have to do this and would like to achieve
the same results using VBA.

Following advice from this newsgroup I've tried:
outputTo option but can't see any options for outputing to .dbf (all other
options do not export the ole object)
TransferDatabase command but it requires a .dbf database to already exist.
SQL methods using the INTO command. This does export to a .dbf file but it
does not export the OLE objects.
I've tried combinations of creating the database using SQL and then using
the TransferDatabase but this comes back with erros about the .dbf being
read-only (checked the properties and read only not ticked)

So basically I'm stuck and would really appreciate some more suggestions.

Thanks,

Gez

 

[Exponent]

How about creating an ADO recordset then rs.save...

 

[Gez]

Never thought of that, thanks!

My only problem now is that the path of the connection string may vary, I'm
using the currentproject.connection string at the moment but I will be
porting the Application to VB later and I don't think that will work. Any
tips on how to tackle the referencing of a database file that can be
installed to a custom path?

Cheers,

Gez

 

[Exponent]

How will this VB app find the database ? Can you use the same approach ?
Maybe you can find the database in (or relative to) the path of the application/executable ? or passed
as a command-line parameter, or opened using a menu, or stored in the registry...
Much depends on the design of the application.
--
_______________________________________________________
http://www.ammara.com/
Image Handling Components, Samples, Solutions and Info
DBPix 2.0 - lossless jpeg rotation, EXIF, asynchronous

 

[Brendan Reynolds \(MVP\)]

This is a VB.NET example, but while the details would differ slightly, the
same general approach would work in VB6, or in Access. An MDB ("Options.mdb"
in this example) that lives in the same folder as the application stores the
path to the main data MDB. You could, of course, instead of the
"Options.mdb" database, use an INI file, a config file, the registry, or any
other data store that your application can access. The key is that it lives
at a known location.

Private mstrDataSource As String

Private Sub StudentMain_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim objConnection As System.Data.OleDb.OleDbConnection
Dim objCommand As System.Data.OleDb.OleDbCommand
Dim strDataSource As System.String
Dim ofd As System.windows.Forms.OpenFileDialog

Try
objConnection = New System.Data.OleDb.OleDbConnection
objConnection.ConnectionString = "Provider =
Microsoft.Jet.OLEDB.4.0 ; Data Source = " & Application.StartupPath &
"\Options.mdb"
objCommand = New System.Data.OleDb.OleDbCommand
With objCommand
.CommandText = "SELECT OptionValue FROM tblOptions WHERE
OptionName = 'DataSource'"
.CommandType = CommandType.Text
.Connection = objConnection
.Connection.Open()
strDataSource = CStr(.ExecuteScalar)
.Connection.Close()
End With
If Not System.IO.File.Exists(strDataSource) Then
ofd = New System.Windows.Forms.OpenFileDialog
With ofd
.DefaultExt = "MDB"
.Filter = "Microsoft Access Databases (*.MDB)|*.MDB"
.RestoreDirectory = True
.Title = "Where is the data file (DSCCDT04.MDB)?"
End With
If ofd.ShowDialog <> DialogResult.OK Then
MessageBox.Show("The application can not continue
without locating the data file. Please consult your administrator or contact
technical support.", gstrcApplicationTitle, MessageBoxButtons.OK,
MessageBoxIcon.Information)
Application.Exit()
Return
Else
mstrDataSource = ofd.FileName
With objCommand
.CommandText = "UPDATE tblOptions SET OptionValue =
'" & mstrDataSource & "' WHERE OptionName = 'DataSource'"
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
End With
End If
Else
mstrDataSource = strDataSource
End If

'Application specific code.

Catch ex As Exception

'Error handling code.

Finally

'Clean-up code.

End Try

End Sub

--
Brendan Reynolds (MVP)
(e-mail address removed)

How will this VB app find the database ? Can you use the same approach ?
Maybe you can find the database in (or relative to) the path of the

application/executable ? or passed

 

[mahmut tekin]

Hi,

I've posted a number of requests on this one and got some great responses
but its not quite there yet.

Background:

I have a table containing field1 (picture description) field2 OLE objects
containing picture.
The table is dynamic in that it can be updated by a user with new images and
descriptions but I want to offer the flexibility of saving the table to disk
so that it can be import again at a later date.

The most promising way I've found is by using the export menu option and
performing the following:

Create a new .dbf file which you can name
Import the table back in.containing the OLE objects in the table

This proves that it can be done.

Obviously I don't want the user to have to do this and would like to achieve
the same results using VBA.

Following advice from this newsgroup I've tried:
outputTo option but can't see any options for outputing to .dbf (all other
options do not export the ole object)
TransferDatabase command but it requires a .dbf database to already exist.
SQL methods using the INTO command. This does export to a .dbf file but it
does not export the OLE objects.
I've tried combinations of creating the database using SQL and then using
the TransferDatabase but this comes back with erros about the .dbf being
read-only (checked the properties and read only not ticked)

So basically I'm stuck and would really appreciate some more suggestions.

Thanks,

Gez