Data Access Page - SQL SELECT / INSERT

[Guest]

I have a form in Access that displays a record from a Data Entry table.

I created a module that would select the record from the Data Entry table
(as displayed in the form), and append it the Master Data table. I created a
macro to run this module, and linked it to a button on the form where it was
activated by ‘OnClickâ€. It all works fine.

I then created a "DATA ACCESS PAGE" in MS Access from this form.

I had to tweak the control on the form that has the “OnClick†feature so
that it is set to run a VBScript shown below. When the user clicks on the
“Submit†button, I want to append the data from the data entry table into the
Master table.

It is not currently working….No error messages....just nothing happens.

Any help much appreciated.

Thanks!
David

sqlSelect = "SELECT BIA_DATA_ENTRY.* INSERT INTO BIA_MASTER FROM
BIA_DATA_ENTRY WHERE ID > ' 0 ' "

 

[Ken Snell [MVP]]

I think you omitted a lot of the VBScript? What you posted is incomplete if
you were wanting to run a query via VBScript.

 

[Guest]

Hi Ken,

I'm pretty new to VBScript, so I may have inadvertently not included
everything I needed for this function.

What else do I need in order to do what I'm trying to do......insert the
contents of the table bound to the form to another table?

Thanks for your help.

David

 

[Ken Snell [MVP]]

You need to execute the query via an ADO command. Here is some generic
VBScript to get you started:


SAMPLE VISUAL BASIC SCRIPT CODE TO OPEN A RECORDSET TO AN ACTION QUERY,
AND TO EXECUTE THAT ACTION QUERY. CAN DO IT BY USING A STORED QUERY OR
BY BUILDING THE SQL STATEMENT WITHIN THE CODE.
----------------------------------------------------------------------



dim rst


Dim vbs_adParamInput
Dim vbs_adInteger
dim vbs_adCmdStoredProc
dim vbs_adCmdText
Dim vbs_adOpenDynamic
Dim vbs_adLockOptimistic
Dim vbs_adOpenForwardOnly
Dim vbs_adOpenKeyset
Dim vbs_adOpenStatic
Dim vbs_adLockReadOnly
Dim vbs_adLockPessimistic


vbs_adParamInput = 1
vbs_adInteger=3
vbs_adCmdStoredProc=4
vbs_adCmdText=1
vbs_adOpenDynamic=2
vbs_adLockOptimistic=3
vbs_adOpenForwardOnly=0
vbs_adOpenKeyset=1
vbs_adOpenStatic=3
vbs_adLockReadOnly=1
vbs_adLockPessimistic=2


set rst=createobject("adodb.recordset")


'****
' THIS CODE LINE USES AN SQL STATEMENT AS THE BASIS FOR THE RECORDSET (IT'S
ALL ONE LINE HERE!)
'rst.Open "INSERT INTO tblWellAttributeDetails ( WellID, WellAttrTypeID,
WellAttrValue ) SELECT 1 AS Expr1, 12 AS Expr2, False AS Expr3;",
msodsc.Connection, vbs_adOpenDynamic, vbs_adLockOptimistic

'****
' THIS CODE LINE USES A STORED QUERY AS THE BASIS FOR THE RECORDSET (IT'S
ALL ONE LINE HERE!)
rst.open "query1", msodsc.Connection, vbs_adOpenDynamic,
vbs_adLockOptimistic

msodsc.Execute rst
rst.Close
Set rst = Nothing

--

Ken Snell
<MS ACCESS MVP>