Make Table Query

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Make Table Query

Post by D Willett »

It's been a while, hi all.

Problem. I have a vba routine to make certain tables. The routines run and on stepping through I see the usual system messages:

'You are about to run a make-table query.............etc'
'The existing table ..........etc'
'You are about to post ........etc'

But two of the make table query's get to 'The existing table will be deleted.....' and skips over 'You are about to paste.....'
The two mktable query's in question look at a different database via linked tables.

If I manually run the two querys by double clicking them in the editor they work fine.

Code: Select all

'DoCmd.SetWarnings False

DoCmd.OpenQuery "mktblStaff", acViewNormal, acEdit
DoCmd.OpenQuery "mktblAttPeriod", acViewNormal, acEdit
DoCmd.OpenQuery "mktblHoliday", acViewNormal, acEdit
DoCmd.OpenQuery "mkTblWorkGroup", acViewNormal, acEdit
DoCmd.OpenQuery "mkQryAdditionalRef", acViewNormal, acEdit
DoCmd.OpenQuery "mkQryCourtesyAvailability", acViewNormal, acEdit
DoCmd.OpenQuery "mkQryDBA_VR_HireHistory, acViewNormal, acEdit"
DoCmd.OpenQuery "mkQryJCVExport", acViewNormal, acEdit
DoCmd.OpenQuery "mkQryWorkshopControlFit", acViewNormal, acEdit
DoCmd.OpenQuery "mkQryWorkshopControlPaint", acViewNormal, acEdit
DoCmd.OpenQuery "mkQryWorkshopControlPanel", acViewNormal, acEdit

'DoCmd.SetWarnings True
Has anyone seen this before ??
Cheers
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Make Table Query

Post by HansV »

Hi Dave,

What happens if you remove the apostrophe from

Code: Select all

'DoCmd.SetWarnings False
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Make Table Query

Post by D Willett »

Hi Hans, long time buddy, I hope you are well.

I get error 3211 meaning the table can't be deleted, it is in use somewhere.
I'm looking at options to empty the table and then repopulate.

So the contents of tblStaffCore need to be put into tblStaff, that would work.

Ive got to:
DoCmd.RunSQL "DELETE * FROM tblStaff;" for the moment.

Cheers
Cheers ...

Dave.

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Make Table Query

Post by D Willett »

Ho about this Hans, does it seem safe to you:

DoCmd.RunSQL "DELETE * FROM tblStaff;"
DoCmd.RunSQL "DELETE * FROM tblAttPeriod;"

DoCmd.OpenQuery "apptblStaff", acViewNormal, acEdit
DoCmd.OpenQuery "apptblAttPeriod", acViewNormal, acEdit

apptblStaff and apptblAttPeriod are Append query's ??
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Make Table Query

Post by HansV »

Hi Dave,

I'm enjoying my retirement :) I hope you are well too.

I was going to write "If you delete existing records first, you can then use an append query instead of a make-table query." which is exactly what you have come up with in the meantime :thumbup:
Go for it!
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Make Table Query

Post by D Willett »

I'm glad you are well Hans.

This always happens, I have a problem and find the resolution just before you tell me how to fix it :rofl: We must be telepathic.

Take care buddy and have a great Christmas & New Year. :thumbup:
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Make Table Query

Post by HansV »

It called POP: the Power of Posting...
(It really works: posting a question often leads one to think more clearly about the problem)

Happy Holidays to you too! :thankyou:
Best wishes,
Hans