Help needed on writing/updating MS Access 97

Help needed on writing/updating MS Access 97

Post by Jeffery Che » Thu, 24 Jul 1997 04:00:00



Hi:

I am writing a program to access MS Access '97 database using VB5.0.  What
I intend to do is to read from a text file and reformat it, then insert the
data into a table in Access.  However, should the record exist before, I
would do a single field update instead of an insert.  Would appreciate if
someone can show me how I can perform the above with speed as I intend to
insert approximately 150,000 records.

Would appreciate if you can write to me directly with solutions.  I have
look just about everywhere, but still unable to perform the above task.

Thanks,


 
 
 

Help needed on writing/updating MS Access 97

Post by Darrell Spec » Thu, 24 Jul 1997 04:00:00


Jeffery,

Here's a try.  This assumes that the data is in one Access table and that
table is indexed on the field(s) that you need to search to find if the
record already exists. If these assumptions are wrong, then you cannot use
a Table-Type recordset nor its Seek method.

I have not tested this so you'll have to fix my bugs.

Good luck,
Darrell Speck, MCSD

dim rs as recordset
dim iFile as integer
dim sBuffer as string
dim iID as long
dim sStuff as string

iFile = FreeFile
Open "c:\myfile.txt" for input as #iFile

set rs = db.OpenRecordset("c:\mydb.mdb", dbOpenTable)
ws.BeginTrans   ' ws = DAO.WorkSpace variable that you set when open database

with rs
do while not Eof(iFile)
  LineInput #iFile, sBuffer

  'Parse sBuffer
  iID = clng(left$(sBuffer, 6))         'say first six characters holds ID
number
  sStuff = right$(sBuffer,7)           'rest has other stuff

  'See if a record already exists for this ID - assumes that PrimaryKey is
indexed on ID
  .Seek iID

  if .NoMatch then
    'add new record
    .AddNew
    .Fields("ID") = iID
  else
    'already in database
    .Edit
  end if
  .Fields("Data") = sStuff
  .Update
Loop

end with
ws.CommitTrans


> Hi:

> I am writing a program to access MS Access '97 database using VB5.0.
What
> I intend to do is to read from a text file and reformat it, then insert
the
> data into a table in Access.  However, should the record exist before, I
> would do a single field update instead of an insert.  Would appreciate if
> someone can show me how I can perform the above with speed as I intend to
> insert approximately 150,000 records.

> Would appreciate if you can write to me directly with solutions.  I have
> look just about everywhere, but still unable to perform the above task.

> Thanks,




 
 
 

Help needed on writing/updating MS Access 97

Post by Terry Kref » Sat, 26 Jul 1997 04:00:00


Once you've manipulated the data, append the lot to a temporary table in
the access database, then use an Update query to amend the fields which are
different in your existing table, then use an Append query from your
temporary table onto your existing table to add the new records, then clear
down the temporary table.

The above method of using queries, especially if you can use parameterised
queries which have been executed and saved in your database will be faster
than using DAO.



> Hi:

> I am writing a program to access MS Access '97 database using VB5.0.
What
> I intend to do is to read from a text file and reformat it, then insert
the
> data into a table in Access.  However, should the record exist before, I
> would do a single field update instead of an insert.  Would appreciate if
> someone can show me how I can perform the above with speed as I intend to
> insert approximately 150,000 records.

> Would appreciate if you can write to me directly with solutions.  I have
> look just about everywhere, but still unable to perform the above task.

> Thanks,



 
 
 

1. MS-Access 97 to SQL Server to MS-Access 97

Hi this is a thread from another news group that has not been
answered. Any ideas anyone?

Thank You, Thank You...you hit it right on the money. It seems SQL
import/export converted all my MS-Access memo fields to the SQL ntext
data type. Does SQL Server have a comparable memo field type? If not
how do people get around the memo field requirement? Create text field
large enough for the average requirement?

Thanks Again

On Thu, 17 Jun 1999 16:40:30 +0400, Alexey Dolganov

2. Internal Procedures Accessing Temp-Tables?

3. Universe: ANALYZE.FILE question

4. I Need help insering text and dates into MS Access '97

5. Filemaker Pro Help Needed

6. Need help in connecting to MS ACCESS 97...

7. UNIX Compatibility and/or C++ class library

8. Need help with MS Access 97 tab controls

9. Writing java.sql.Timestamp to MS Access 97.

10. Write conflict error when updating data from SQL Server 7.0 in Access 97

11. Help, using an Access 97 DB without MS Access Application