Text 2285, 169 rader
Skriven 2005-02-01 17:19:18 av Mike '/m' (1:379/45)
   Kommentar till text 2281 av Antti Kurenniemi (1:379/45)
Ärende: Re: Anyone handy with Access and ODBC?
==============================================
From: Mike '/m' <mike@barkto.com>

On Tue, 1 Feb 2005 13:34:50 +0200, "Antti Kurenniemi" <antti@anttik.com> wrote:

>"Mike '/m'" <mike@barkto.com> wrote in message
>news:05gtv0p13ad2jtsu63g4tadp4d5d0dla0m@4ax.com...
>> I'm confused.  You say you want: "I was thinking that it should probably
>> be
>> possible to connect directly from Access and just push the numbers to the
>> other database."
>>
>> Yet you also say: "but there were about 17 trillion hits, and most of them
>> seemed to be about how to open a MySQL database with Access which is not
>> what
>> I'm after."
>>
>> Those two seem to contradict each other, or am I just reading them wrong?
>
>No, I'm probably just explaining it badly. The latter means just using
>Access as a front-end for MySQL, but there's only one database (MySQL) open.
>What I need is a way to have data in Access and an open connection from that
>database (using VBA most likely) to MySQL. So two database connections from
>Access, one "native" and one through ODBC.
>
>
>> fwiw, I have used the MySQL ODBC connector to allow Access's VBA
>> to move data from an Access local database to a MySQL server on a
>> different machine.
>
>Yay, that's *exactly* what I mean. So it's possible, great. Easy? I can do
>ODBC stuff with other programming languages, but I haven't really done
>Access VBA programming, so that's the side I'm looking info for.


OK, here is my code and my comments on how to use it.  Big caveats - (1) I
learned Access VBA on this code, and (2) it was one-time only usage, so it just
had to work once while I watched it. In other words, it is ugly. References to
Bugzilla mean Bugzilla on MySQL.


==================================================================

I established a ODBC connection from Access to Bugzilla using MyODBC.
http://www.mysql.com/products/myodbc/

I linked the Bugzilla tables to Access using File|Get External Data| Link
Tables    (DO NOT use Get Data here).  Select all the tables.
Note that, for some reason, I have to unlink longDescs table, and relink it by
itself (i.e., not as part of a "select all").  If you don't do this, then, for
some reason, Access does not have write access to the table.  Whenever I was
prompted for an index field, I always accepts the default that Access
presented.

At this point, I have the single bug table in Access, an ODBC link to the
Bugzilla database, and a backup of the Bugzilla database.

I then run a VBA module in Access that copies the data from the Access table to
the Bugzilla tables.  That VBA module's source is at the end of this message.

Run a Sanity Check in Bugzilla, everything should look OK.  There may be some
Unsent mail.  If so, click the link to send it. (I could never figure out how
what value to set in Bugzilla to avoid the Unsent Mail warning in Sanity
Check).  Also, if you have a lot of unsent mail, it make take quite a while to
send it all.   Be patient.

I ran Sanity Check once or a twice a day for the first week, and it showed no
problems.

I realize the above has gapping holes in detail where I presumed you have prior
Access/ODBC/etc. knowledge.  This was not intended to be a detailed HOW-TO, but
more as a description of one approach to solving the problem.

And now, the conversion VBA code:

===================================================
Option Compare Database
Option Explicit


Public Function doConvert()


' leave the following commented out to trigger
' Access default error handling which puts up a nice dialog
' box and allows you to enter the VBA debugger
'On Error GoTo Error_Exit  ' what can possibly go wrong?

Dim localdb As Database   ' localdb is the Access bug database
                          '   with the ODBC links to Bugzilla
Dim rstOPEs As Recordset  ' Access table containing all the bugs Dim rstBZ As
Recordset    ' Bugzilla "bugs" table
Dim rstLD As Recordset    ' Bugzilla "longdescs" table



Set localdb = CurrentDb

' this is the Access denormalized bugs table
Set rstOPEs = localdb.OpenRecordset("OPEs")

' these are the Bugzilla tables on MySQL
Set rstBZ = localdb.OpenRecordset("bugs") Set rstLD =
localdb.OpenRecordset("longdescs")


rstOPEs.MoveFirst

 Do While Not rstOPEs.EOF

  ' only process open OPEs with priority > idle
  If IsNull(rstOPEs!CompletionDate) And rstOPEs!PriorityFKey > 1 Then

            rstBZ.AddNew
            rstLD.AddNew

            rstBZ!bug_id = rstOPEs!OPE_ID
            rstBZ!Short_Desc = rstOPEs!ShortDesc
            rstBZ!product_id = rstOPEs!bz_prod_id
            rstBZ!component_id = rstOPEs!bz_comp_id
            rstBZ!bug_severity = rstOPEs!bz_severity
            rstBZ!priority = rstOPEs!bz_priority
            rstBZ!creation_ts = rstOPEs!SubmitDate
            rstBZ!bug_status = "NEW"

            rstBZ!Version = "unspecified"
            rstBZ!everconfirmed = 1

            rstBZ!reporter = rstOPEs!bz_reporter
            rstBZ!qa_contact = 9
            rstBZ!assigned_to = 2

            rstLD!bug_id = rstOPEs!OPE_ID
            rstLD!who = rstOPEs!bz_reporter
            rstLD!bug_when = rstOPEs!SubmitDate
            rstLD!thetext = rstOPEs!VerboseDesc


            rstBZ.Update
            rstLD.Update




  End If

  rstOPEs.MoveNext
 Loop



Exit Function


Error_Exit:
    MsgBox ("An error occurred")

   Exit Function



End Function
===================================================

If you have any questions, don't expect me to know too much.  :-)

 /m

--- BBBS/NT v4.01 Flag-5
 * Origin: Barktopia BBS Site http://HarborWebs.com:8081 (1:379/45)