Solved: Create loop for insert query

[XLGibbs]

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...

[Bob Phillips]

Since you are using Access to "house" the SQL statements, he is offering alternatives that are out there to potentially skip the process being in Access, but I am not sure you have those resources at your disposal (such as Visual Studio or another script writing software tool that allows programming in VB, Java or other non VBA language...

Access does not "house" the SQL statements, it "houses" the VBA that is used to pass the SQL string to the database. Access is really two products, the db back-end, and a forms front-end. The back-end understands SQL, but you don't need to run Access to use that aspect, you can do that from anywhere (as we all know with Excel).

If he has some tool to write ASP, then he has a tool to write VBScript. For goodness sake, it only needs a text editor, he isn't writing complex apps that would benefit from tools like Visual Studio.

[better]

I couldn't seem to get any of these solutions to work for me. I did, in my research, come across the syntax for the DoCmd.RunMacro action, which has a repeat count argument. If I could figure out how to execute a macro from my ASP application using VB script, that would be ideal, as I define the repeat count argument as Request.Form("NumDoors") easily enough. Does anyone know how to go about this?

XLD, I am using Macromedia Dreamweaver 8 for my ASP/VB Script development. My problem isn't so much lack of access to tools, it's lack of knowledge!

[better]

I finally got it!

Created a macros named "InsertDoors" with RunSQL "INSERT INTO ACS_Doors ( JobName, Location, DoorName, DefaultID, DefaultLabel ) SELECT ASPTable.JobName, ASPTable.Location, ASPTable.Door, ASPTable.DefaultID, ASPTable.DefaultLabel
FROM ASPTable;" and "DeleteASP" with RunSQL "DELETE ASPTable.* From [ASPTable];

and executed it from my ASP page with this code:

[vba]
<%
Dim DbPath
Dim objAccess
Dim NumDoors
Dim macroName
Dim macro2Name
%>
<% Set Session ("objAccess") = Server.CreateObject("Access.Application")
DbPath = "/dbfolder/dbname.mdb"
Session("objAccess.Visible") = True
Session("objAccess").OpenCurrentDatabase Server.MapPath(DbPath)
macroName = "InsertDoors"
macro2Name = "DeleteASP"
NumDoors = Request.Form("NumDoors")
Session("objAccess").DoCmd.RunMacro macroName, NumDoors
Session("objAccess").DoCmd.RunMacro macro2Name

Session("objAccess").CloseCurrentDatabase
%>
<% Set Session("objAccess") = Nothing%>[/vba]
So Gibbs, thanks so much for the idea of using the temp table approach, it's working beautifully!



Bart

[XLGibbs]

Great job!

Happy to have,err, helped?

You get all the credit on this one..nice job.

Pete

PS. If this is a done issue, just mark the thread solved for us, thanks!