Very Computer

Hi,
I want to be able to use a local mdb table to hold the contents of a
recordset. I was hoping to read the fields from the recordset and delete/add
fields in my temp table.  It works about 9 times and then tells me that
there are 'too many fields defined'. I don't understand why because I'm
deleting before adding.  The error appears during the add column process.
The recordset has 27 fields and the routine dies on the 9th time while
adding the 12th field which makes 255.  However when the routine works I
only see 27 fields and printing the count during the routine gives 27.
After it bombs I must compact the database for the routine to work again.

Also I could not figure out how to use ADO to remove the 'Required' setting.

I want to use the temp table to run access reports. Most users are not
allowed to see some columns in the SQL7 tables but I figured I could login
as a user with the permissions, make an ADO recordset and bind the report to
the recordset.  Binding the report to the recordset was appearing to be
impossible but using the local temptable was perfect.

If there is a better way of doing this I would not mind any comments.
Thanks
Brian

Sub LoadTempTable(rsData As ADODB.Recordset)
    Dim cnnBEdb As ADODB.Connection, cnnFEdb As ADODB.Connection
    Dim rsTemp As ADODB.Recordset
    Dim catCurr As ADOX.Catalog
    Dim tblTemp As ADOX.Table
    Dim aFld As ADODB.Field
    Dim i As Integer, NoOfCols As Integer, NoOfNewCols As Integer
    Dim mySQL As String

    Set cnnFEdb = CurrentProject.Connection
    OpencnnDB cnnBEdb                                'connect to the
SQLServer with full read permission

    Set rsTemp = New ADODB.Recordset
    Set catCurr = New ADOX.Catalog
    Set tblTemp = New ADOX.Table

    'empty the table 1st
    cnnFEdb.Execute "DELETE * FROM tmpTable;", , adCmdText
+adExecuteNoRecords

    With rsData
        NoOfNewCols = rsData.Fields.Count - 1
        catCurr.ActiveConnection = cnnFEdb
        With catCurr.Tables("tmpTable")
        With tblTemp
            NoOfCols = .Columns.Count - 1
            'delete the existing columns
            For i = 0 To NoOfCols
                .Columns.Delete (0)
            Next i
            'put in the new ones to match the recordset
            For i = 0 To NoOfNewCols
                Set aFld = rsData.Fields(i)
                .Columns.Append aFld.Name, aFld.Type, aFld.DefinedSize
            Next i
        End With

        'Use DAO to remove 'Required' setting
        For i = 0 To NoOfNewCols
            CurrentDb.TableDefs("tmpTable").Fields(i).Required = False
        Next i

        'now copy the records from the SQL7 rs to the temp table in my local
MDB
        rsTemp.Open "tmpTable", cnnFEdb, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
        Do While Not .EOF
            With rsTemp
                .AddNew
                For i = 0 To NoOfNewCols
                    .Fields(i) = rsData.Fields(i)
                Next i
                .Update
            End With
            .MoveNext
        Loop
        End With
    End Sub

In your case you might be better off deleting the temp table and then
creating a new temp table instead of emptying the table.    

FWIW why not use a temp MDB to hold that temp table?  And build and
delete the MDB and table as required.  See the sample code at my
website for such.

One advantage is this would help in bloating your local MDB.

For that matter why not just delete and recreate the table?

Tony
----
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
   VolStar http://www.volstar.com Manage hundreds or
thousands of volunteers for special events.

     Because of a gross Injustice that I am not supposed to have to bear and
face at all, I am left trapped in a viscous circle here, of this legal
matter, unable to ever have basic system onus Charge details or any approach
to it so far.

    Because of and in the rare situation here caused by gross total
interference with a Federal Trial process, I need a specialist lawyer before
any charge details exist as pertainable to so far this 9 years of the
matter, before I can yet begin or pertain to the matter at all.

    I am supposed to provide the cost and work of supplying that for the
matter
at this stage. Can you help or direct me to who does help?
Feel free to send comments to:  mijcha...@bigpond.com

     For example, I have posted it to nearly 2,000 lawyers recently and
still have no lawyer for it. Yet a lawyer and/or system onus is necessary
and in fact ruled requisite.

    To fully effectively deal in terms of this matter, competence is
required in areas of Australian Federal, Administrative, Appeal, Damages and
Criminal Law. It is the system's onus to redress those deficiencies, but so
far the system and its associates have refused their onus, or to help.

   In the 9 years of the matter so far, nothing adequate has yet been
supplied for Charge basic data. I am entitled to have that basic systen onus
Charge data process deficiency redressed so as, interalia, I can at last
pertain to and begin the matter as the onus of the charge I am there to
face.

     As I see it I need a lawyer to take the case to the High Court on 3
points and then form a case for damages. I would pay well in bonus's from
any damages got.

    The 3 points are

  -1- the question of the whole '91 Charge attempt being a Nullity.

 - 2 -the question of the judge Taylor in '94 who forced himself and a
Denovo trial on the started trial of my Review appeal in Judge Wall's '93
court. That question entails that Taylor is not valid as a charge and trial
I face, and, that Taylor in the Federal Law here, is Invalid to the extent
of his inconsistency with the relevant requisite law, of the same Judge,
Wall, having to hear the started Trial process.

 - 3 - The point that Judge Wall's '93 ruling of requisite specialist legal
aid before the matter proceeds further, is a significant valid ruling.

     Then there would be some things to sort out, things that if the above 3
points were to be upheld, that would need High court statement further than
just a basic law upheld.

     Then we could consider the case for damages.

    :It is a C/wealth/Federal Law matter, NSW purporting to exercise the
Invested jurisdiction. It started out with a significantly Null charge
attempt which claims a Strict Liability charge to represent the Mens Rea
only C/W Crimes Act Provision  85(s)(b).

   Although even that is fairly arguable, b/c the # on the charge sheet says
provision 855 and the c/w crimes act only goes to 125 or so. But the point
has never had any chance of argument or process so far.

   And further the totally senseless even within itself charge purported
itself as
any opinion at all of a letter within its 4 corners, regardless and
exclusive of the letter's circumstances, nothing even established or known
of its circumstances. The police/prosecution attempting to claim a Public
censorship scope for a letter's written surface alone, during Postal usage.
They did not even have any postal usage to use for it, but didn't realise
that for months.

      Claiming to be made under Section 855 C/Wealth Crimes Act, The Charge
 read,  -  that I,

  -----------------" On the 11th day of october, 1991, at Wentworth in the
state of
 nsw, did use a postal service supplied by australia post, in such a way as
would have been regarded by reasonable persons in all circumstances as
offensive. "----------------------------

      That is the complete, exact wording and punctuation, and applying to
the surface of a letter within its 4 corners. As is the data supplied as the
police brief surrounding those charge words to set the situation and nature
of the charge, by and at the Committal hearing in December '91.

  - 1 - In '92, The Magistrates penalty was a $400 fine.

  - 2 - In '93, in the started trial process of my Review appeal on
questions of Law, the Judge, Wall, ruled the charge attempt appeared to be a
total Nullity with no scope of procedure, but that the factors it involved
were beyond the available charging range the unrepresented Defendant has to
face. And in fact
of that unavailability to the charging range that the charge attempt is
complicatedly so of that unavailability to a degree requisiting and
necessitating specialist legal aid and representation before the matter can
proceed any further. And Wall then adjourned the matter mid Trial, until
that specialist aid
eventuated. Wall then set the matter for recommencement before himself one
year later.

  - 3 - In '94, the  false judge Taylor crazily forced himself and a Denovo
trial on
my started Review appeal trial in Judge Wall's court. Taylor also purported
a
 penalty. It was the maximum allowable 3 year good behaviour bond, the
first 18 months of which was to be served being supervised by corrective
services, again the 18 months is the maximum .

      Already since '91 the situation has falsely caused me to have had to
spend all the money I had trying to supply basic Charge sheet and level
details for the matter, and that as the only approach the matter allows at
all so far and still. Since '91, b/w $50 -2,500 a week, in contact with up
to 5 depts a week, for no result so far, and now I am broke. That is
something it is not supposed to ever at all be my legal and /or financial
onus to have to do in relation to a charge I face.

    As well as being worth approx $11,000,000,000(billion) in damages at
made out current Australian rates, it still has all the above of its
requisite and basic process to be sorted out for the basic charge data angle
to exist yet.

     :That is not an exaggeration or embellishment of what the charge is. It
very totally is that, only reads as that, was intended as that, insisted on
as that, and it is provable as that and that it was intended as such. Of the
relevant such circumstances of writing of a letter, in this matter, I have
never been asked yet, how and why I wrote the letter. It is not established
or known at all if the people and places mentioned in the letter exist at
all. No people or anyone claimed as such people have ever been present in
court at all in this matter.

   The nearest thing to anything established or known of the letter's
circumstances is that a mystery woman, not of the same name as on an
envelope, or in the letter, took an envelope and letter to a police station
300
miles from the envelope and letter's physical relevance.

  That is all this matter knows and has ever heard of her and how and why
that was. I do not know who, how and why she is. As far as I know none of
the Police concerned knew or know any more of her than that.

  Police then, presuming postal usage from that, and with no interest
whatsoever in the letter's circumstances no matter what those circumstances
were, thought they had the above cited postal usage letter's surface viewing
charge available to them, and so purported to proceed with that natureless,
degreeless, peopleless, circumstanceless, totally nullity senseless through
and through yes/no question and answer charge attempt.

  Sic, _ _ ' that people viewing the letter's written surface only, would
find its words offensive, no matter what the letter's circumstances were ' _
_ .

   The magistrate at the 1st court appearance Aug '92 refused to allow any
of the explanation necessary for the charge to exist at all even within
itself. She forcibly applied the prima facie senseless charge, resulting in
multiplying the already extensive, complicated situation and still we had no
explanation or even definition of anything to use or pertain to.

   I then Review appealed it. NSW purported to supply the District Court for
this. A thing it very much had no right to do. There is no jurisdiction in
the District court for Federal Review appeal, and no scope for the State
exercising the power to purport there is.

  At the Review appeal,in the specifically opened, started trial processs,
the Judge, Wall, upon a brief cursory look at the surface data, stated the
obvious, and said it appeared the charge was a Nullity, because there is no
scope of changing the Charge Nature(Strict Liability) to the Mens Rea nature
the Law the prosecution purports the charge to represent, 1914 C/w Crimes
Act 85(s)(b) solely is and allows.

  Wall then further stated that the mess the charge data was in and the
elements it involved were beyond the available charging scopes that the
unrepresented defendant has to face. Wall granted a one year's adjournment
and set the matter for recommencement before himself again one year later.

  Due to the matter occurring in a distant complicated border situation,
Legal
 aid refusing to pay attention to Wall's ruling, and me having a serious
permanent back injury then, I could not get a lawyer in the interim year.

   The next year a different judge, Taylor, without explanation, crazily
forced himself and a Denovo trial on the matter. He refused to allow any
explanations, and did not supply any definitions of his actions at all. Nor
did he even manage to supply anything for the charge as his complicated
vocal only attempts were that undefined, senseless, contradictory and
unsatisfactory at all even within itself. But he vastly multiplied the
already extensively in need of explanation entire situation.

      My right to have basic charge and jurisdiction system onus process is
being denied astronomically crazily here, and is significant even in the
National and International Human rights criteria as operatively requisiting
redress for this particular individual matter.

     For example, to
...

read more »