Another Solution? Linking?

[John]

Dear Group,
I have a situation that I think might have a better solution than the one
I have been proposing.

Situation:
I am writing a VB 6 application that runs ADO against an Access 97
Database.
The client wants to upload flat files (CSV) into the Access database
programatically.

Current Approach:
- linked table to flatfile.
- Select all records and process the records into the Access Database as a
different table.

Problems:
- Client and Manager will not consider DAO for the code. Must be done in
ADO
- Files may reside at different locations from the oringinal link and client
wants the process to be automated with minimal technical setup required.

Any help would be appreciated.

John.

 

[Arvin Meyer]

Client and Manager need to defer to your experience. If they are so good at
databases they need to do it themselves rather than hiring you.

In point of fact, DAO has been around a lot longer than ADO. It works with
more diverse databases, and, whether they like it or not, is likely to be
around a lot longer than ADO which is already set to defer to ADO.NET in the
next version or 2.

If you were able to use the "latest and greatest" ADO version against an
Access 97 database, you might have a shot. As it is they'll either need to
upgrade to Access 2003, or let you do it the best way you know how.

That said, you could link the CSV table, prebuild an append query in the Jet
backend, and run the query from your VB6 front end.

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[John]

If I use Stored Procedures (Queries) to perform the append. How do I
determine if a record is an update or insert?

Thanks for your help.

john.

 

[Arvin Meyer]

If you use stored procs, you'll either need to use ADO or OBDCDirect (in
Access 97) check the help files for "ODBCDirect", or you can use
Pass-Through Queries to the Stored Procs.

The syntax is totally differend between an Update, which is done on existing
records, or an Append which Inserts new records.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Doug T.]

You could really get tricky by firing off a timer to check if a table has
been uploaded. If it is newly uploaded, open the file as a table, then
process it as you would any other database.
To determine if a insert or update, you could add this to the file title.
**********
'treats a file of type customers_insert.csv as insert file
'treats a file of type customers_update.csv as update file
Set objConn = New ADODB.Connection
Set sourceRS = New ADODB.Recordset
Dim path As String
Dim action as string
dim actionTable as string
CommonDialog1.Filter = "Comma Separated Values (*.csv)|*.csv"
CommonDialog1.ShowOpen
path = Left(CommonDialog1.FileName, (Len(CommonDialog1.FileName) -
Len(CommonDialog1.FileTitle) - 1))

If InStr(CommonDialog1.FileTitle, " ") Then
MsgBox "Please edit the file name to have no spaces before importing
it."
Else
objConn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & path
& ";Extensions=asc,csv,tab,txt;HDR=NO;Persist Security Info=False"
sourceRS.Open "SELECT * FROM " & CommonDialog1.FileTitle, objConn,
adOpenKeyset, adLockReadOnly
End If

'now check what type of data, insert or update
action = right(commondialog1.filetitle, len(commondialog1.filetitle -4))
'action is insert or update
actionTable = lcase(left(commondialog1.filetitle,
len(commondialog1.filetitle - 6))) 'what table are we talking about
If right(action, 5) = "insert" then
select case actionTable
case "customers"
'must have sent a insert file for customers
end select
elseif right(action, 5) = "update" then
'must have sent a update file
endif
***********
Note that when opening txt, or csv files this way, you cannot have spaces in
the file name. As this would create the SQL statement like "SELECT * FROM
TABLE NAME" which is unacceptable. "SELECT * FROM TABLE_NAME" is OK.

Easiest way is to use JRO, fire off a create replica, send the data to a ftp
site or and automatically synchronize the DB's using a small service.

I had made a program that actually used CDO through e-mail to add, update
and delete records. Every day a slave computer would e-mail its updates
from transactions for the day. On the home computer system, the email would
be received (checked for authenticity) and the updates would be added to the
master database. The master could also e-mail requests to the slave for
specific data, with the slave automatically responding via e-mail the
datasets asked for. This implementation was necessary for a location in
another country where internet access was not reliable and very expensive by
the hour.

Doug

 

[John Smith]

Dear Group,
Thanks for the suggestions. As has been stated earlier, I need to import
into an Access97 DAtabase. My problem is that I already have 150 ADO
classes built that handle all interaction between 100 tables (not including
the 50 tables that need to be updated from flat files. The options
presented so far revolve around using DAO to modify a link to the flatfiles
and importing them through updates.
In a single file upload, I can have either Updates Or Inserts. Using
both DAO and ADO requires rewriting 150 classes. I am looking for an easier
way to allow for uploading of flat files without having to resort to
rewriting the application.

My problems with stored procedures is handling the records that will error
because of pre-existance (in the case of an insert) or non-existance (in the
case of update).
This does not even handle the problem of the file existing in a different
path than the standard location for the application to update.

Due to the complications revolving around linking, pathing, and need to
control the state of data. I believe I may have to scrap the idea of
linking to the table and open the datafile directly and insert the data
manually to get the performance and control this application needs. I was
really hoping to find an easier way of doing it.

I welcome any comments.

John.