Can this be done with a database

[mtarby]

I know how to connect to a database to write a txt file or spreadsheet, but someone has asked me if its possible to write the contents of a sql database table into an Access file.

Possible?

I haven't come up with anything that says yes so far....

Thanks in advance.

Michelle

 

[ToddWW]

What do you mean by Access file ? Do you mean a file that Access can import or do you mean actually insert records directly into the Access table.

ToddWW

 

[mtarby]

I guess either - I know the person that's asked for it could definately import the data into Access if given a file. I think the more I think about it, I'm leaning toward giving him a xls or cvs file for hime to import unless there's a better way...

 

[FesterSXS]

You can export data into an Access MDB file from within SQL Server Enterprise Manager. Is that an option or do you not have direct access to the SQL Server?

Tony
________________________________________________________________________________

http://www.SpiraxSarco.com

 

[ToddWW]

Yes, well my assumption is that the Access database can be connected to from your web application. If so, simply create a connection to that database, and use SQL INSERT to create records in his database.

But I'm guessing that's not the case. Therefore, CSV is going to be your best way to go. I don't know how to format CSV for Access so you'll want to determine that first. In other words, do you need to surround text field with "" or dates with something else, etc..

Once you determine that, you can use the following code to create a CSV file from an open recordset.

Let's assume you have an open recordset and the recordset object is called objRst

dim objFso
dim theFile
dim sF

Set objFso = Server.CreateObject("Scripting.FileSystemObject")

Set theFile = objFso.CreateTextFile("absolutePathToFile\yourfile.csv", True)

WHILE NOT objRst.EOF
  sF = objRst("field1") & "," & objRst("field2") & "," & objRst("field3")
  theFile.writeLine(sF)
  objRst.MoveNext
WEND
objRst.Close
set objRst = Nothing

theFile.Close
set theFile = Nothing
set objFso = Nothing

If you've got a zillion fields in your recordset, you might want to use the FOR EACH i IN objRst method to write the value of each field followed by a comma. Then before you write the line to the file, remove the last comma from the end.

I place the commas in there between the field values to conform to the Comma Seperated Value format.


ToddWW