copying table structure in VBA

[ChrisB]

Hi. I'm hitting my head into the ground here.
Maybe the biggest problem is Access 2000, but I have no choice in the
version at this time. anyway, the basic question is how do I connect an
ADODB.Recordset to the current Access database? (please read last paragraph
for code sample if that's all you will answer)

I need to copy a table structure. At first, in code, I made a copy of a
table to another name. Then I "delete * from newtable" and proceeded to
"insert ..." to populate the table. The point is that I have multiple insert
statements that put data in a specific order, too complex for just 1 "order
by" clause. While debugging, it always works perfectly. While running without
breaking, the order of the data inserted is always RANDOM!! I'm thinking it
has to do with maybe that data is never REALLY deleted from the tables, that
it's only marked, and that inserting "un marks it?" Don't know. Tried putting
in delays.. didn't help.

so I now am trying to copy the structure by code. Using DAO I have
problems.. like if the field type is 20 (which is supposed to be "Number" but
not sure what type=20 really is) the code spits out an error when doing
something like: tDest.Append( theField )

So trying ADO. But I can't get passed a very simple connection problem. Can
anyone answer this one? This is what I have for the code, and it simple
doesn't work. No errors, but rs.RecordCount is always -1 no matter what query
I run:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = CurrentProject.Connection
Call cnn.Open
rs.ActiveConnection = cnn
'rs.ActiveConnection = CurrentProject.BaseConnectionString
'rs.ActiveConnection = CodeProject.Connection
Call rs.Open("select * from " & sSourceTable)

If rs.RecordCount > 0 Then
' it never gets here.. recordCount is always -1
' And YES, there IS data in this table! Any query I run will result in -1
rs.MoveLast
rs.MoveFirst
End If

 

[George Nicholson]

When unspecified, the default CursorType for Recordset.Open in ADO is
forward-only.

The Help entry for ADO Recordcount states that the property will return -1
for forward-only cursors.

Try adding the following (or one of its variants) before rs.Open:
rs.CursorType = adOpenStatic '(or adOpenDynamic or adOpenKeyset)

HTH,

 

[ChrisB]

OH MY GOSH!! I completely forgot!! I've been using ADO in VB 6.0 a whole lot,
and maybe the version of ADO I'm using sets recordCount to 1 if the query
succeeds, or maybe instead of forwardonly I had readonly.. I don't know..
Anyway that gets me passed that! I can't believe it!

Now if this structure copying in ADO works, that would be nice. We'll soon
see.

Thank you!

 

[ChrisB]

well.. another nutty question.. how do I create an ADO table and then append
it to the current Access database? I know how to do this with DAO.. simply by:
currentdb.tabledefs.append( theTableDef )

But not sure about ADODB.

 

[ChrisB]

well I think I'm on the right track. Trying to utilize ADOX.Catalogs to do
this.

 

[ChrisB]

well I just discovered THIS way of copying a table structure:
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name,
acTable, strOldTableName, strNewTableName, True

However, still does not solve my problem, because doing the inserts still
comes up in RANDOM order!!!! it's like Access is trying to multithread the
insert statements (because stepping through, it's always in perfect order)
I turned off what I think are options in the source QUERY that might do this.

 

[ChrisB]

ARGH!! I can't believe it!!

now to try to FORCE the output to be in order, I create temp tables.. 1 for
each "insert". They get created just fine with the right data. Now all I want
to do, is COPY the data from these temp tables into the main table, and
APPEND that data!! You can choose that option when you do a copy/paste in the
GUI.. how the hell do you code it?? That Transferdatabase command doesn't
have the option to append. The copyObject() command doesn't either!! I hate
Access 2000!

 

[George Nicholson]

What order are you trying to maintain, and more importantly, why?

Relational table data is unordered. When looking at a table directly, the
last "sort" options may still be applied. Maybe you simply need to apply a
new sort (or index to the sorted field). Even so, "table order" is
unreliable and purely cosmetic. Rely on queries to determine the "order" of
your data output.

Data is either in the table or it isn't. Where it is in the table should be
of no significance.