I though this WAS in Access. But you still have to open a connection to the database as follows...change the ObjRs.Open strSql as below..

[VBA]
Public Sub LoopInsert()
Dim objRS As Recordset
Dim strCount As Long
Dim x As Long

Dim strConnect as string

Set objRS = New Recordset


StrConnect = ?Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\UDLSample.mdb;Persist Security Info=False?

strSQL = "Select * From [ASPTable]"

ObjRS.Open strSQL ,strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText


strSQL2 = "Insert Into ACS_Doors ([JobName],[Location],[Door],[DefaultID]) Select [JobName], [Location], [Door], [DefaultID] From [ASPTable]"

strCount = 99999 '<=====Intentionally large number
Do

strCount = objRS![NumDoors]
For x = 1 To strCount
DoCmd.RunSQL strSQL2
Next x

Loop

strSQL3 = "Delete * From [ASPTable] "
DoCmd.RunSQL strSQL3
End Sub

[/VBA]

Change the C:\UDLSample.mdb to the path of your database...