DoCmd.RunSQL

[locutus]

does anyone understand this coz i have been playing about for a awhile and being kack at VB i keep getting errors, so could someone do a quick example of this statement and explian it coz the only one i can find is in the help file, and that is no help at all.

cheers again.

 

[Jacob Mathai]

Docmd.runsql "Select * into temp1 from master"

or

dim strsql
strsql = "Select * into temp1 from master"
docmd.runsql strsql

 

[locutus]

thanks for that, i got that to work but that replaces all the fields in the new table, what i need it to do is add new fields to the end of the table.

is it possible to use variables and read data into them so i can validate it, and then insert it to the new table? if so how?

cheers again

 

[Jacob Mathai]

My favorite method is to have a table with all the columns I need. Let us call it tblMaster.
Assume I imported a table by name tblTransx.

I will delete all the rows in tblMaster and then insert all rows in table tblTransx into tblMaster

1. Docmd.runsql "delete * from tblMaster"

2. docmd.runsql "Insert into tblMaster Select Name,unit,qty, amount as ext_price from tblTransx"

The advantage is that you retain all attributes of table tblMaster.

This is just one method. I am not sure if it will meet your specific need.

 

[locutus]

thats cool, but for the fact i do`t wont to and will get my arse kicked if i delete data.

so is there any way of reading one row of data into corresponding variables then inserting that into the new table

i have done a similar thing to this in ASP when at uni but that was year ago and that not in VB.

help please coz this is pissing me off now
thanks again

andy

 

[simongallop]

Look at recordsets

Dim MyRS as Recordset (DAO.Recordset for Acc2k)

Set MyRS = CurrentDB.OpenRecordset("TableName",dbOpenDynaset)
MyRS.AddNew
MyRS("Field1Name") = Info for 1
MyRS("Field2Name") = Info for 2
etc...

MyRS.Update
MyRS.Close


HTH

[This message has been edited by Harry (edited 03-22-2002).]

 

[Rich]

Why not just use an append query?

 

[3divine]

Just a comment for Harry:

Confused?

I thought DAO.Recordset is for Access 97 (
Dim rst As DAO.Recorset) and

ADODB.Recordset is for AYK2? (Dim rst As New ADODB.Recordset) ?

 

[simongallop]

You might be right, but I have found that in Acc97 I can get away with Dim rst as Recordset yet in 2k I had to use DAO.Recordset to be able to use:

Set rst=CurrentDB.OpenRecordset("Table",dbOpenDynaset)