Create Table

[Guest]

Thanks to help I found in this forum, I created some VB code that appends
data from one subform to a table within another subform when I click on the
"Save" button. This code works wonderfully.

The problem I'm having is, after this code creates this table, I'm trying to
run a different query that creates another table using the data that is in
the first table I created.

I'm getting an error - Run-time error: '3211' The database engine could
not lock table 'tbl_High_Vol_Disc' because it is already in use by another
person or process.

Here's the code:

Dim strSQL As String
Dim stDocName As String
Dim RsC As DAO.Recordset

Set RsC = Me.RecordsetClone

RsC.MoveFirst
While Not RsC.EOF
Me.Bookmark = RsC.Bookmark

If Chk_Add = -1 Then

strSQL = "INSERT INTO [tbl_PVD_Updates](AcctNo, ItemNo,
ProjectNo,
[Date], Charge_Units, Unitpr, Rprice)" & _
"select" & "'" & txt_AcctNo & "'," & "'" & txt_ItemNo & "',"
& "'" &
txt_ProjectNo & "'," & "'" & _
txt_Date & "'," & "'" & [txt_Actual Units] &
"'," & "'" & txt_Unitpr
& "'," & "'" & txt_rprice & "';"

DBEngine(0)(0).Execute strSQL, dbFailOnError

End If

RsC.MoveNext
Wend

Set RsC = Nothing
'Works great up to here.

stDocName = "qry_PVD_Discounts" 'Code stops working here.
'Uses "tbl_PVD_Updates" to create new table,
"tbl_High_Vol_Disc".
DoCmd.OpenQuery stDocName, acNormal, acEdit

[Forms]![frm_PVD]![frm_PVD_Import].Requery
[Forms]![frm_PVD]![frm_High_Vol_Disc].Requery

Me.Refresh

I hope I explained this clearly enough. Any help would be greatly
appreciated.

Thanks,
Rachel

 

[Jeff Boyce]

Rachel

It would be a highly unusual relational database design that required
creating tables. Usually, the tables (and the relationships) are already
set, and the data comes and goes from the tables.

If you'll provide a bit more description of the "what" (what data you are
working with, what business need you want to meet), rather than the "how"
(forms, subforms, create tables, ...), the readers here may be able to offer
alternate approaches that make better use of Access' relationally-oriented
features/functions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi Jeff,

Thanks for the quick response. You're right, it's not the best way to go
about it.

My boss used to figure out the final amounts in an excel spreadsheet, then
manually enter the them into the "tbl_High_Vol_Disc" table in Access.

I automated it by gathering data from 2 different sources, and running the
calculations for him. I was trying to populate the same table that he used
to enter data into ("tbl_High_Vol_Disc"). I was going to create the table so
it was deleted and recreated with the new data. I thought it would be
easier than changing the queries that already used that table. I knew it
wasn't good, but I was rushed and don't have much time and/or experience.
I've decided to use a query instead of making another table.

Again, thanks for the response. I think I'm all set now.

Rachel

Rachel

It would be a highly unusual relational database design that required
creating tables. Usually, the tables (and the relationships) are already
set, and the data comes and goes from the tables.

If you'll provide a bit more description of the "what" (what data you are
working with, what business need you want to meet), rather than the "how"
(forms, subforms, create tables, ...), the readers here may be able to offer
alternate approaches that make better use of Access' relationally-oriented
features/functions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks to help I found in this forum, I created some VB code that appends
data from one subform to a table within another subform when I click on
the
"Save" button. This code works wonderfully.

The problem I'm having is, after this code creates this table, I'm trying
to
run a different query that creates another table using the data that is in
the first table I created.

I'm getting an error - Run-time error: '3211' The database engine could
not lock table 'tbl_High_Vol_Disc' because it is already in use by another
person or process.

Here's the code:

Dim strSQL As String
Dim stDocName As String
Dim RsC As DAO.Recordset

Set RsC = Me.RecordsetClone

RsC.MoveFirst
While Not RsC.EOF
Me.Bookmark = RsC.Bookmark

If Chk_Add = -1 Then

strSQL = "INSERT INTO [tbl_PVD_Updates](AcctNo, ItemNo,
ProjectNo,
[Date], Charge_Units, Unitpr, Rprice)" & _
"select" & "'" & txt_AcctNo & "'," & "'" & txt_ItemNo &
"',"
& "'" &
txt_ProjectNo & "'," & "'" & _
txt_Date & "'," & "'" & [txt_Actual Units] &
"'," & "'" & txt_Unitpr
& "'," & "'" & txt_rprice & "';"

DBEngine(0)(0).Execute strSQL, dbFailOnError

End If

RsC.MoveNext
Wend

Set RsC = Nothing
'Works great up to here.

stDocName = "qry_PVD_Discounts" 'Code stops working here.
'Uses "tbl_PVD_Updates" to create new table,
"tbl_High_Vol_Disc".
DoCmd.OpenQuery stDocName, acNormal, acEdit

[Forms]![frm_PVD]![frm_PVD_Import].Requery
[Forms]![frm_PVD]![frm_High_Vol_Disc].Requery

Me.Refresh

I hope I explained this clearly enough. Any help would be greatly
appreciated.

Thanks,
Rachel