By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,706 Members | 2,046 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,706 IT Pros & Developers. It's quick & easy.

RecordSet and SQL Insert Query Trouble

P: n/a
After having googles I've knocked together ther following code. What I
am trying to do is select all items in one table and move through them
adding them into other tables (splitting them and putting different
parts in different table linked together with relationships).

THe bits that I can't figure out are:
1) how to get info from the recordset (i.e. select info from that to
put into the other tables using insertSQL)

2) how to solve the Error: 424 I get on Line (Set WorkRS1 =
WorkBase.Ope.....)

I'm more of a php man so the code to me would be:

while($Var = mysql_fetch_array($record_set)) {
$sql = "INSERT INTO table ([Field]) VALUE (" &
$Var["FieldNameFromRecordSet"] & ")"
}

But I'm taking my first leaps into access.

Private Sub Command1_Click()
Dim strSQL As String
Dim insertSQL As String
strSQL = "SELECT ContactFirst,ContactSurname FROM tbl_Contacts ORDER BY
ContactSurname"

Set WorkRS1 = WorkBase.OpenRecordset(strSQL)

Do Until WorkRS1.EOF

' INSERT INTO CLIENTS TABLE
insertSQL = "INSERT INTO tbl_Clients
([ClientName],[ClientAddress1],[ClientAddress2],[ClientAddress3],[ClientTown],[ClientCounty],[ClientPostcode])
VALUES ('','','','','','','');"

'INSERT INTO CONTACTS TABLE
WorkRS1.MoveNext
Loop

WorkRS1.Close
WorkBase.Close

End Sub

Jun 29 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a

Sockpuppet wrote:
After having googles I've knocked together ther following code. What I
am trying to do is select all items in one table and move through them
adding them into other tables (splitting them and putting different
parts in different table linked together with relationships).

THe bits that I can't figure out are:
1) how to get info from the recordset (i.e. select info from that to
put into the other tables using insertSQL)

2) how to solve the Error: 424 I get on Line (Set WorkRS1 =
WorkBase.Ope.....)


Why would you want to use code at all for this? Well, other than
executing a series of SQL statements?

I'd just use a series of append queries and that should do it. No
recordsets required.

Something along the lines of

Sub SplitData()
dbEngine(0)(0).Execute "INSERT INTO...",dbFailOnError
End Sub

Looksto me like you're making things a lot harder than they need to be.

Jun 29 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.