Access Database file conversion

[Saurabhk]

Hi guys,

I've got a database which frequently need to be sent over the internet so that other users can update their data. As the database is huge with more than 30,000 records, it's really hard for other people to download it as they are on dial up. Is there a way of converting database or maybe just the tables into different file format for faster transfer.

Thanks for your time

[OBP]

Ideally you need to share this database by having it web based, it would make life much easier.
In the mean time here are some ideas.

Is the database currently being "Zipped"?

Have you thought about sending only those records that the user needs to update? Are the records "registered" in any way to the user?

You could export the just the tables to a blank database and send that.

You could Output the records to an Excel Spreadsheet and send that, it should be smaller than sending the whole database.

You could write some VBA code for importing from the "table only" database and VBA and a query to import the data from Excel into your real database.

If the Records were "linked" to the user you could totally automate the process of outputting thier records in to an Excel spreadsheet and then emailing the spreadsheet to each of them. There version of the database would have the same code but they would all send it to you.

[Saurabhk]

Hi OBP,
Thanks for replying
I'll tell you what the real situation is. We've got a centeral web based database from which I'm getting all my records. I run queries on this web based database to create my own tables. I had to create my tables because I've created some reports which involve some complex queries. If I run reports directly from the main database, it takes a lot of time to open reports.
Now my problem is that when I log in the database as my user name all the tables that I've created are updated from the central database but for all other users as they have a very slow internet connection I had to disable this option as it was taking hours for them to update tables.
As the other users don't have much knowledge about databases, I have to come up with a way of data transfer easy enough for them to understand.
Is there a way I can convert all the tables into a text file or something and send the data to them and they can run a query and their tables get updated.
I don't really know what to do.

[stanl]

Sounds like an ideal situation for using Disconnected Recordsets. Stan

[OBP]

stanl, I do not know anything about disconnected recordsets.
Saurabhk, I have a 7Mb Excel file of 60000 records that zips down to 1Mb, would that be too big for them to download?

[stanl]

stanl, I do not know anything about disconnected recordsets.


Basically it is a way to allow users to work remotely on subsets of a central database without being directly logged in. For example, suppose I had a table called prospects with a field for region or salesid to identify the person responsible for adding/updating records. So I create an ADODB.Recordset (oRS) with a connection to my db and issue

SELECT * FROM Prospects where salesid='Stan';

Then issue oRS.Save c:\temp\stan.xml,1

which persists a modified xml format (really an ascii file but behaves like a recordset) which I zip and email to Stan and he works with it as a local table, send it back and I re-open and issue UpdateBatch() against the main table. I was thinking maybe Saurabhk had this sort of situation since he indicated making multiple query tables which were distributed to users. If not, I just made a bad guess.

[OBP]

stanl, that sounds perfect for this application, can the recordset be "zipped" as well?

[stanl]

stanl, that sounds perfect for this application, can the recordset be "zipped" as well?

Sure, if necessary. It's a matter of architecture. You see disconnected recordsets a lot in .asp pages, or in Client-Server situations like Oracle where licenses are a premium - [you can have 50 users updating tables with say only 10 licenses] - You would have to know a lot more about the data/user structure in this situation before going ahead with that strategy. Stan

[Saurabhk]

Hi OBP,
Thanks for the reply.
So what you suggesting is to transfer the data from the table to excel file, zip them and then distribute them among all the users?

Hi Stanl,
Thanks for the help. I don't think I have much idea about what you are suggesting but I'll do my research about the disconnected recordset. I think its definitely worth a shot.

Thaks a lot both you guys I really appreciate

[Saurabhk]

Hi Stanl,

I just did a bit of research and came up with the following code.

Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Set Conn = New ADODB.Connection
Conn.Open "DSN=movex"
Set Rs = New ADODB.Recordset
Rs.CursorLocation = adUseClient
'Rs.Open "Select * from "
Rs.Open "Select * from PESALES", _
Conn, _
ADODB.adOpenForwardOnly, _
ADODB.adLockBatchOptimistic
Rs.Save "J:\Power Equipment\Common\SK\db2.txt"
Rs.Close


Is this what you were suggesting?

[stanl]

You might wish to start someplace like here

http://www.4guysfromrolla.com/webtech/080101-1.shtml

which has a Part2 and several related articles concerning updates and xml streams. Also, you have to decide on a container to be used by persons the data is being distributed to. In my experience I would run SELECT queries from Oriacle or AS400, send local files to users who would perform adds/edits with local .html forms and vbscript, then I would perform batch updates at the end of the day from their local files. Let me see if I can put together a practical example for you to practice with.

[Saurabhk]

Thanks a lot stan. If you could come up with an example, that would really help

[Saurabhk]

Hi Stanl,

I've been researching on the disconnected recordsets but still haven't got much luck. I used the following code to save the recordset in a txt file



Private Sub Command0_Click()
Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Set Conn = New ADODB.Connection
Conn.Open "DSN=movex"
Set Rs = New ADODB.Recordset
Rs.CursorLocation = adUseClient
Rs.Open "Select * from PESALES", _
Conn, _
ADODB.adOpenForwardOnly, _
ADODB.adLockBatchOptimistic
Rs.Save "J:\Power Equipment\Common\SK\db2.rs"
Set Rs.ActiveConnection = Nothing
Rs.Close
End Sub


But I don't know how I can send this recordset to another user for him to update his table. So far I've come up with the following code but I don't know if I'm any close



Private Sub Command1_Click()
Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Set Conn = New ADODB.Connection
Conn.Open "DSN=movex"
Set Rs = New ADODB.Recordset
Rs.ActiveConnection = Conn
Rs.CursorLocation = adUseClient
Rs.Open Source:="J:\Power Equipment\Common\SK\db2.rs", Options:=adCmdFile
Rs.UpdateBatch
End Sub



Thanks for your help

[Saurabhk]

Is it possible to create a table or overwrite an existing one using a saved recordset?

[stanl]

Is it possible to create a table or overwrite an existing one using a saved recordset?

Yes, and in answer to your previous post with a disconnected recordset you use the MSPersist Provider (comes with MDAC) to modify rows, then set the ActiveConnection to your main database and issue updatebatch().

Do the following
1. Create a temp table on your database and fill it with say 5 rows of dummy data.
2. Use ADO code to open the table as keyset,batchoptimistic [ 1,4 ]
3. use Recordset .Save "C:\temp\mysample.xml",1 (you can change the path, but keep the 1 as the second parameter.)
4. zip-up mysample.xml and attach it to this thread (and, while you are at it, open it up in notepad and take a look at the xml structure)

Stan

[Saurabhk]

Hi Stanl,

I've created the mysample.xml file with recordset CursorType = 1 and LockType = 4. How do I go about from here now. My code now looks like



Private Sub Command0_Click()
Dim Conn As ADODB.Connection
Dim RS As ADODB.Recordset
Set Conn = New ADODB.Connection
Conn.Open "DSN=movex"
Set RS = New ADODB.Recordset
RS.CursorLocation = adUseClient
'Rs.Open "Select * from "
RS.Open "Select * from AUTHENTICATION_TEST", _
Conn, 1, 4 'RS.CursorType = 1 , RS.LockType = 4
RS.Save "J:\Power Equipment\Common\SK\mysample.xml", 1
Set RS.ActiveConnection = Nothing
RS.Close
End Sub

[stanl]

Mysample.xml is a disconnected recordset. So pretend I'm one of your remote users and attach (hopefully like I asked, some sample data) Mysample.xml here as a zip. I'll modify/add a few rows then send it back w/ instructions for updating the main DB. Again, use sample data. Stan

[Saurabhk]

Hi Stanl,

The other users don't need to alter any data, they just need the file so that they can update their records. Is it possible to use the .XML file to replace the data in their existing table.

[stanl]

Hi Stanl,

The other users don't need to alter any data, they just need the file so that they can update their records. Is it possible to use the .XML file to replace the data in their existing table.

Absolutely.

[Saurabhk]

Hi Stanl

How can I do it? Can you please give me some sort of example.
My code for creating the XML file is as follows



Private Sub Command0_Click()
Dim Conn As ADODB.Connection
Dim RS As ADODB.Recordset
Set Conn = New ADODB.Connection
Conn.Open "DSN=movex"
Set RS = New ADODB.Recordset
RS.CursorLocation = adUseClient
'Rs.Open "Select * from "
RS.Open "Select * from AUTHENTICATION_TEST", _
Conn, 1, 4 'RS.CursorType = 1 , RS.LockType = 4
RS.Save "J:\Power Equipment\Common\SK\mysample.xml", 1
Set RS.ActiveConnection = Nothing
RS.Close
End Sub


I just want to change the data of the same table I created the file from ie AUTHENTICATION_TEST

Thank you

[stanl]

With some reluctance, I have attached an example zip file. I really think you need to perform more research on Disconnected Recordsets prior to using them in your own system. I have used them for several years with great success so it is second nature to me. That being said.... the zip has 3 files

1. acctsnew.mdb - has 2 tables, accts and temp. temp is just a copy of accts so you can retry the module code.

2. acctsnew.xml - an xml file which has the 3rd record changed (so examine the accts table in the mdb first)

3. acctsnew.xls - which has a udate() module that can be run from the macro menu. The module will open and update the mdb. NOTE: I hard-coded all files to run from c:\test so make changes on your PC when you try the macro [I wanted to keep things simple].

The important thing to note: Since an xml disconnected recordset is a text file, the trick to updating a table in a different DB that is not the same as the original the recordset was disconnected from is to wrap the rs:data with <rs:insert> .... and </rs:insert>. [In my experiences, users normally update there own recordset, and send changes back to the main DB, so that step is not necessary. I used file I/O to insert the two lines.

I was used to situations where users did not have Access installed on there system so DAO was not an option, MDAC and ADO were since they were free. Also, the module code was run from a .wsc - also free.

In conclusion. This is a sample for using disconnected recordsets in situations where main database tables are distributed to remote sites. Stan

[Saurabhk]

Thanks a lot for that Stan. I know I still need to learn a lot. I appreciate the effort you've put in.

Thanks again

Regards
Saurabhk