ASP: export data to Ms Access

Tweet

Just about a year ago, I published an article describing how to export data to Excel with asp. In this article we are going to do the same, but using Ms Access as a target.
There are some things we need to prepare before starting creating the page that will actually export the data. That is because our Ms Access database (I will refer to it as mdb from now on) must be created before doing anything. At the same time, we need to create a connection to the target mdb.

Preparing the target

First of all, we have to create a target mdb that we can call template.mdb. The Ms Access file will have a table which will be our target table. The columns of the target table should be where we are going to put the data. Just to be more clear, we should look into our source table and reproduce it in our target mdb. As a start, I suggest to re-create all the fields as text data type. That is because data types might differ from the source table (e.g. a SQL Server table). If the data types are not compatible, we will end up with an error. If you start to understand the basic usage explained here, then you will be able to adjust your target mdb with the correct data types.


After creating the mdb, place it in the same folder where we are going to put the asp file (we will call the folder on your server as our working directory). This folder must have read and write permissions.
We also need to create a connection to the target mdb. I suggest to create a DSN connection on your server, so that our connection string will be:

conn = "dsn=nameofdsnconnection;"

Where nameofdsnconnection is the name of your DSN connection.
This is important: the DSN connection is not to "template.mdb" but to the file we are going to create. In our working directory, we will end up having a template.mdb, an export.asp and a final.mdb. The latter is our final Ms Access database. The DSN connection should point to that file.
Now we are basically ready for export.

Get the source data

Create a new asp page and call it export.asp. Include all the relevant connections (one to the source database and the DSN connection created following the above instructions). Insert the code needed to fetch the data from your source database and create a repeat region. We are going to insert the recordset into the target database, one record at the time. We are going to create a box that will be update for every record inserted, so that the user will be aware of what's happening.

The export.asp

Now we are going to insert the code needed to export the data. First of all, we set the paths to the template mdb and the final mdb:

<%
path=server.mapPath("./")
targetDB = path & "final.mdb"
sourceDB = path & "template.mdb"
set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile sourceDB, targetDB, true
set fso = nothing
%>

Basically we have copied the template.mdb to final.mdb. That will preserve our template.mdb.
In the body section of your asp file, insert:

<%
response.write "MDB CREATION IN PROGRESS<br>"
response.write "Inserting record number:"
%>
<form id="form1" name="form1" method="post" action="">
<input name="c" type="text" id="c" value="0" size="4" maxlength="4" readonly="true">
</form>

That will be our progress meter.
We then initialise the insert command:

<%
Set Insert = Server.CreateObject ("ADODB.Command")
Insert.ActiveConnection = conn
%>

As you can see, the active connection is the DSN connection we created before. Now we insert the command text. That will be the sql query needed to insert the source into the target.

<%
Insert.CommandText = "INSERT INTO nameoftable (field1, field2, ...)  VALUES (" & (sqlsource.Fields.Item("field1").value) & ", " & (sqlsource.Fields.Item("field2").value) .... "

This is a bit tricky, so be careful when creating the command text. You should already know how to create an insert query. However be careful with data types. In the above example, we are inserting into the target table of our final.mdb (nameoftable) numeric fields (field1 and field2). If we need to insert text data, we need to put ' around the values:

& ", '" & (sqlsource.Fields.Item("field3").Value)& "', '" &

Please notice the ' around the Fields.Item.
Again, in the above code sqlsource is the name of the source recordset (I assumed that we are fetching the data from a SQL Server table).

Another thing. The Insert.CommandText must be inserted inside our repeat region; so put the above code just after the beginning of the repeat region.

Ok. Let's move on and let's execute the insert command:

Insert.CommandType = 1
Insert.CommandTimeout = 0
Insert.Prepared = true
Insert.Execute()
%>

And then, we update our progress meter:

<script>
document.getElementById('c').value=<%=RepeatIndex+1%>
</script>

Where RepeatIndex is your repeat region index. Notice that the input created above has an id="c" and that we change the value by getting the element by its id (getElementById).
We then insert a response flush:

<%Response.Flush()%>

And close the repeat region.
Running the export.asp in your browser, it will create a final.mdb file with the same structure of template.mdb, but containing the data fetched from a SQL Server table, for example.

And that is all. If you need help or encounter some kind of problem, please share your thoughts in the comment section below.