Use a Macro to Create a New Table

[xcrx]

I am trying to create a new table using a macro however everything i run the function it tells me that the table can't be locked because it is already in use. I added a command to close the form the macro is launched from because the table is used on that form but I still get the same error.

I am creating the table using do.cmd RunSQL

This is the function I am using to create the new table;

Function CreateTable()

DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT qryTotalPartsUsed.PartNumber,  qryTotalPartsUsed.Ordered INTO tblOnOrder FROM qryTotalPartsUsed"
DoCmd.RunSQL "CREATE INDEX PartNumber ON  tblOnOrder(PartNumber) With  Primary"
DoCmd.SetWarnings True
End Function

Any ideas on how I can get around this?

 

[HiTechCoach]

Instead of a make table query and then create an index, I woudl recommend just deleting all the records with a delete query. Append the record into the table. This way the table will already exist with the index. If a form is opened using the table then all you have to do is requery the form to see the new recordset.

 

[xcrx]

Thanks. I will try that.

Will that fix my problem with the table not being able to be locked?

 

[HiTechCoach]

Thanks. I will try that.

Will that fix my problem with the table not being able to be locked?

Yes.That is correct.

 

[xcrx]

I tried that and it seems to work just fine so thank you for that.

I am having another problem now though. I want the form to reopen to the same record it was on before the macro was run. I thought I could do this by assigning the record number to a TempVar called PreviousRecord and then then use the FindRecord command but it keeps telling me the the object "TempVar" does not exist.

In the "find what" section of the macro I put =[TempVar]![PreviousRecord]. I tried to do it without the brackets around TempVar but it puts them back when I delete them.