Re: Copying records from one DB to another, ADO, VB6

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


"Jennifer Ward" <jward@xxxxxxxxxxx> wrote in message
news:%23QIsbvrtIHA.5500@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

I have two identical databases (two mdb's) where I want to essentially
merge
part of one into the other based on an SQL string that does some filtering
by a 'Date' field.

There are 60 fields in the database so I'd like to avoid moving data by
having to code the name of each field.

I can only imagine two scenarios:

(1) Perhaps create a temporary table in the destination DB containing
only
he info I want and then do some sort of 'simple' merge operation, then
delete the temporary database.
(2) Open each database at the same time and somehow copy records by record
using rs.AddNew and rs.Update. I tried this but can't figure out how to
copy the entire record at one time.

I'm running VB6 on Windows XP using ADO and Access databases of course.

Any help would be appreciated.

Thanks. Jennifer


Well. There are two other options.

1) Use DAO instead of ADO. For a local mdb, DAO will be faster and simpler
than ADO.
"How To Copy a DAO TableDef Including User-Defined Properties"
http://support.microsoft.com/kb/q217011/

Here's another sample of the simplicity:
Dim tdf As DAO.TableDef
For Each tdf In CurrentDb.TableDefs
' some sort of filter for what you want ...
' here where are just going to skip over the system tables
If Not Left$(tdf.Name, 4) = "MSys" Then
DoCmd.TransferDatabase
transfertype:=acExport, _
DatabaseType:="Microsoft Access", _
DatabaseName:="C:\Test.mdb", _
ObjectType:=acTable, _
Source:=tdf.Name, _
Destination:=tdf.Name, _
StructureOnly:=True
End If
Next tdf

DAO, Jet and MSAccess were made for each other. (Literally. <g>)
You don't have to use DAO exclusively in your app. It is ok to use DAO for
the 'DDL' and Maintenance work and ADO for the fetch 'n carry work. Just try
and avoid chewing at same time on the same item with two different models.
<g>

2) If you just have your heart set on ADO then use ADOX - "'Microsoft ADO
Ext. 2.6 for DDL and Security".

Equally straight forward - looks very much like the DAO model only with
different names. Plenty of examples on Google of how to go about using it.

There is a "Gotcha"! Although well explained in the docs it seems to bite
everyone the first time out. So here's a "heads up" - to save some
aggravation, and save you a trip back here. <g>

When you are creating a new Field, many properties beyond the basics (ie,
name size, type, ...) are not available until the Field is appended to a
Fields collection. So it usually takes two passes to set up all the
properties.

Create with basic properties, append, circle back and set extended
properties.

[Makes sense if you think about it. How does a Field know it has a Scale
property, until it first learns it is a Number? <g>]

hth
-ralph



.

Relevant Pages

  • Re: Knowledge Base Help Not working
    ... Database is a DAO object. ... both Access 2000 and 2002 use ADO. ... Microsoft DAO 3.6 Object Library, ... For example, to ensure that you get a DAO recordset, you'll need to ...
    (microsoft.public.access.formscoding)
  • Re: ADO and the Find method
    ... You cannot compare DAO and ADO. ... database, which has OLEDB provider. ... >>>> Opening of the whole table was not a good design anytime. ...
    (microsoft.public.vb.database.ado)
  • Re: Crosstab query error 3104
    ... Thankfully very few compile errors had to ... I have both DAO & ADO references checked but DAO is higher so it is taking ... Thanks - I inherited the original database & am gradually working through ... then perhaps you've removed the reference to ADO. ...
    (microsoft.public.access.queries)
  • Re: How do I use ADO in VC++ 6.0 to get the columns names of the t
    ... you're using DAO rather than ADO ... (ActiveX Database Objects). ... robust library for working with Jet databases. ...
    (microsoft.public.data.ado)
  • Re: Database variable change from 97 to 2003
    ... Access 2003 has references set to both DAO ... and ADO and, perhaps unfortunately, the ADO reference is set higher in ... use Dim rcd As ADODB.Recordset) ... > modules, however, I get errors on the database variable ...
    (microsoft.public.access.modulesdaovba)