Data Access Page - SQL SELECT / INSERT

  • Thread starter Thread starter Guest
  • Start date Start date
G

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 ' "
 
I think you omitted a lot of the VBScript? What you posted is incomplete if
you were wanting to run a query via VBScript.
 
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
 
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>
 
Back
Top