Quick Question - Exporting Access table to file

Hi, just a quick question,

i'm using this code:


Sub Import()

Dim cn As ADODB.Connection
Dim sqlString As String
Dim strFile As String

Set cn = New ADODB.connection

strFile = "C:\db1.mdb"

cn.ConnectionString = strFile
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open
cn.Properties("Jet OLEDB:Max Buffer Size") = 256

'Insert into the existing table
'use 10.0 for XP, use 8.0 for below....

sqlString = "INSERT INTO [tblExcel] (Column1, Column2, Column3, Column4) SELECT Column1, Column2,
Column3, Column4 FROM[Excel 10.0;DATABASE=[C:\Test.xls;HDR=Yes;IMEX=1].[Sheet1$];"

'execute the command and close the connection
cn.Execute sqlString
cn.Close
Set cn = Nothing

End Sub
(part of mkoslof's code)

to export access table to excel file.

My question is, do i need excel application to be installed in the computer that runs the application?
or do i need to deploy some pack with in my installation file to make it usable??

Thanks!

Well..good question :). If the user has office installed, they will have Excel. But, your Excel reference (8.0, 10.0) may cause you problems. I suggest using 8.0 because this driver *Should* work, even in XP. Of course, if the user doesn't have Excel, they wouldn't have the spreadsheet to insert from in the first place. Unless you are installing the spreadsheet within your setup.exe and then, you would need to have Excel installed to open the .xls file.

ok, i'm using 8.0 so this is ok,

So, if final user doesn't have office (and excel) installed they won't be able to "use" this code?
What can i do?? (because installing excel it's not an option :) )

There's any other available format to export access to with the simplicity of your code but without any deployment problems?

Thanks!

Well, there is a fundamental issue then :). Because whether you use this method or the native VBA method TransferSpreadSheet, the user needs to have Excel installed because they want to import a spreadsheet. So the question becomes, why do they even want or need this procedure if they do not have Excel?

Basically, what I would do is surround your Excel import code with the GetObject call. If your call to Excel fails (it is not currently open, and an attempt on CreateObject also fails, etc.), you know it is not installed. Then, you can prompt the user and back out. But again....if a user wants this feature or will be using this feature, I would assume they already have spreadsheets with data and Excel installed.

ok, you're right:

But again....if a user wants this feature or will be using this feature, I would assume they already have spreadsheets with data and Excel installed.

But it's not so easy, because i was using this method to make an access table exportable. I searched for different methods to do it and finally i decided to export access to excel with your code (although the main goal is to export access, not to have an excel spreadsheet) because it was clean and fast.
What i need is a method that allows me to export an access table to a file (any type of file) without installation or application problems, and with manageble dependencies.

Can you help me??

thanks!!

Well, if you want to import to something with ABSOLUTELY no dependencies..meaning the user doesn't have Office installed at all...go with a Text file.

But again, to handle any problems with exporting to Excell, before firing the code, just check if Excel exists on the CPU. Again, use the GetObject/CreateObject methods to test if it is installed. You could also do a select against the Windows Management object to see if the Excel exe is present on the CPU. There are several methods, none more than 2 or 3 lines of code, that will check for the existence of Excel. And, if the user doesn't have Excel, just prompt them and tell them they can not use this method. Then, provide another method, maybe export to a standard txt file, etc.

ok,

thank you for all your help, i'll do what you suggest, having two methods (first check for excel, if it's not installed then use a txt file)

Just one more thing, there's any way to easy export access table to txt file (as you do with excel with just one insert statement) or do i have to do it field by field from database and formating all the file??

Thanks again

For this, I would Open a Txt file and loop the recordset until EOF. Then use the Print command to add data to the new text file. There are many examples of this on the forum...do a search :). Writing out to Text files is very quick and VB is quite good at it. So, I would suggest just doing starndard File I/O methods. If you want, review the File I/O tutorial written by Gavin. But I am sure you can get this working fine :)

And the same goes with ADDING data from a txt to an Access table..just use standard file I/O practices.

Thanks!!

PD: another dinner in your account! :)

How many is that now :) LOL.