Re: copying table structure in VBA
From: George Nicholson (JunkGeorgeN_at_msn.com)
Date: 03/01/05
- Next message: DenBis: "Finding Duplicates Using Loops"
- Previous message: Italian Pete: "page (not) getting the focus"
- In reply to: ChrisB: "copying table structure in VBA"
- Next in thread: ChrisB: "Re: copying table structure in VBA"
- Reply: ChrisB: "Re: copying table structure in VBA"
- Reply: ChrisB: "Re: copying table structure in VBA"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 1 Mar 2005 11:38:14 -0600
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,
--
George Nicholson
Remove 'Junk' from return address.
"ChrisB" <ChrisB@discussions.microsoft.com> wrote in message
news:4B3C7BD3-C344-4D8A-B590-3F39337F9FF6@microsoft.com...
> 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
>
- Next message: DenBis: "Finding Duplicates Using Loops"
- Previous message: Italian Pete: "page (not) getting the focus"
- In reply to: ChrisB: "copying table structure in VBA"
- Next in thread: ChrisB: "Re: copying table structure in VBA"
- Reply: ChrisB: "Re: copying table structure in VBA"
- Reply: ChrisB: "Re: copying table structure in VBA"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
