Not only that, but you can put indexes on the table for consecutive operations thereby speeding things up further down the line.
Liz Ravenwood
Database Developer / Programmer
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
Sent: Sunday, May 13, 2012 3:00 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Running 'make Table' queries at start up
Thanks John,
The option to use a Delete query followed by an Append query works
very well for me.
Cheers,
Robin Chapple
At 12/05/2012 03:36 PM, you wrote:
>Robin-
>
>Ah, that's the problem. It needs to be an Append (INSERT INTO)
>query. You are
>trying to run a Make Table query. Are you reloading the table or
>just adding to
>it? If reloading, you need to run a DELETE * FROM dbo_Bookings first. The
>query should be:
>
>INSERT INTO BookingsMT (BookingID, ConferenceID, [Date], RegistrantID,
>BookingCode, Cancelled)
>SELECT dbo_Bookings.BookingID, dbo_Bookings.ConferenceID,
>dbo_Bookings.Date, dbo_Bookings.RegistrantID,
>dbo_Bookings.BookingCode, dbo_Bookings.Cancelled
>FROM dbo_Bookings
>WHERE (((dbo_Bookings.Date)>#4/1/2012#));
>
>The alternative would be to delete the table before running your Make Table
>query, but then you would lose any indexes defined on the table to
>make queries
>run faster.
>
>John Viescas, author
>Microsoft Office Access 2010 Inside Out
>Microsoft Office Access 2007 Inside Out
>Building Microsoft Access Applications
>Microsoft Office Access 2003 Inside Out
>SQL Queries for Mere Mortals
>http://www.viescas.com/
>(Paris, France)
>
>--------------------------------
>
>From: MS_Access_Professionals@yahoogroups.com
>[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
>Sent: Friday, May 11, 2012 11:44 PM
>To: MS_Access_Professionals@yahoogroups.com
>Subject: RE: [MS_AccessPros] Running 'make Table' queries at start up
>
>
>Thanks again John,
>
>The actual VBA is:
>
>Private Sub Form_Open(Cancel As Integer)
>On Error GoTo ErrHandle
>
>Dim db As DAO.Database
>
>Set db = CurrentDb
>
>With db
>
>.Execute "BookingsQ", dbFailOnError
>
>End With
>
>ExitHere:
>On Error Resume Next
>Set db = Nothing
>Exit Sub
>
>ErrHandle:
>MsgBox Err.Number & " " & Err.Description _
>& " In Procedure Form_Open of MyFormName"
>Resume ExitHere
>End Sub
>
>The SQL of the query is:
>
>SELECT dbo_Bookings.BookingID, dbo_Bookings.ConferenceID,
>dbo_Bookings.Date, dbo_Bookings.RegistrantID,
>dbo_Bookings.BookingCode, dbo_Bookings.Cancelled INTO BookingsMT
>FROM dbo_Bookings
>WHERE (((dbo_Bookings.Date)>#4/1/2012#));
>
>The error message is:
>
>"3010 Table 'BookingsMT' already exists.
>In procedure Form_Open' of MyFormName.
>
>I hope that exposes the problem.
>
>Robin Chapple
>
>At 11/05/2012 05:56 PM, you wrote:
> >Robin-
> >
> >That code looks fine. What is the SQL of the two queries?
> >
> >John Viescas, author
> >Microsoft Office Access 2010 Inside Out
> >Microsoft Office Access 2007 Inside Out
> >Building Microsoft Access Applications
> >Microsoft Office Access 2003 Inside Out
> >SQL Queries for Mere Mortals
> >http://www.viescas.com/
> >(Paris, France)
> >
> >----------------------
> >
> >From: MS_Access_Professionals@yahoogroups.com
> >[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
> >Sent: Friday, May 11, 2012 7:54 AM
> >To: MS_Access_Professionals@yahoogroups.com
> >Subject: [MS_AccessPros] Running 'make Table' queries at start up
> >
> >
> >I have a database that uses a front end that is linked to an on line
> >data source.
> >
> >I use a query to make a table so that I can make reports from fixed data.
> >
> >I need the VBA to use in the "On Load" event of the main form that
> >overwrites the exisiting table.
> >
> >I have tried to adapt advice given a few months ago which used an
> >'Update' query but the code did not like the fact that the table existed.
> >
> >It was based on this:
> >
> >In VBA in the On Load event, do:
> >
> >Dim db As DAO.Database
> >
> >Set db = CurrentDB
> >db.Execute "name of query", dbFailOnError
> >db.Execute "Name of other query", dbFailOnError
> >' etc...
> >
> >Set db = Nothing
> >
> >Many thanks,
> >
> >Robin Chapple
> >
> >------------------------------------
> >
> >Yahoo! Groups Links
> >
> >
> >
> >
> >------------------------------------
> >
> >Yahoo! Groups Links
> >
> >
> >
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>
------------------------------------
Yahoo! Groups Links
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Tidak ada komentar:
Posting Komentar