Multiple DAO / Recordset Problems After Converting to accdb (1 Viewer)

AlexDoran

Registered User.
Local time
Today, 15:04
Joined
Feb 25, 2014
Messages
20
Hi there,

I am in desperate need of some assistance in trying to regain functionality of a large amount of Functions from my Database.

Today i converted my databses Which consist of a Front & Back end to accdb / 2007 from mdb / 2000 in an attempt to try and reduce the frequency of database corruption.

I have worked through a lot of code changes. However i am REALLY struggling with one method of query update (not written by me originally) that was working fine before updating the database files.

When trying to run the SQL / QueryDef Access Simply states Unable To find the output table "tblMaterialIssueRequestLineItems".

This is a table on the back end that is linked to the front end, i can open the table and see all of the data so i am really confused as to why i am being given this error. I have tried to the bellow code to reference CurrentDb instead of DBEngine.Workspaces(0).Databases(0) but am given the same error message. I am witts end with this and there alot of users waiting on a fix for similar functions.

The declarations / QueryDef section:

Code:
Dim MyQuery As QueryDef, alcnrs As DAO.Recordset, isreqrs As DAO.Recordset, matlsrs As DAO.Recordset, _
polirs As DAO.Recordset, grpdstkrs As DAO.Recordset

Set MyDb = DBEngine.Workspaces(0).Databases(0)

If Me![POSTED] = 0 Then

'* Post Request Line Item records

Set MyQuery = MyDb.CreateQueryDef()
MyQuery.NAME = "FILL MATERIAL ISSUE REQUEST LINE ITEMS"
MyQuery.sql = "INSERT INTO [tblMaterialIssueRequestLineItems] SELECT[qryFillMaterialIssueRequestLineItems].* FROM [qryFillMaterialIssueRequestLineItems]"
MyDb.QueryDefs.Append MyQuery
MyQuery.Execute
MyQuery.CLOSE
MyDb.QueryDefs.Delete "FILL MATERIAL ISSUE REQUEST LINE ITEMS"

Any help would be greatly appreciated.

Thanks

Alex
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:04
Joined
Oct 29, 2018
Messages
21,359
Hi Alex. If you manually create a query with that SQL statement, do you still get the same error?
 

AlexDoran

Registered User.
Local time
Today, 15:04
Joined
Feb 25, 2014
Messages
20
Hi there,

Thanks for taking the time to reply!

If i copy that string into SQL View of a query and run it, i get the same error.

thanks

Alex
 

isladogs

MVP / VIP
Local time
Today, 15:04
Joined
Jan 14, 2017
Messages
18,186
You need a space after SELECT

For info, you don't need to create a query def.
Scrap all 7 code lines and just do the append in one line

Code:
CurrentDB.Execute "INSERT INTO ….
 

AlexDoran

Registered User.
Local time
Today, 15:04
Joined
Feb 25, 2014
Messages
20
Well, i feel really stupid right now - but i dont understand how this has happened, possibly when i re-imported all of the tables and data into a new fresh database to try and resolve any corruption.

However the Table name on the Front End was actually "tblMaterialIssueReqestLineItems"......

Again this worked perfectly before i can only assume the table was not imported properly.... (you all probably think im lying).

Running through the whole routine i now get another error saying simply:

Too Few Parameters. Expected 1.

Code:
'*******************************************************************************************************
' Posts the results of the Issue Request entries made to tblMaterialIssueRequestLineItems.
' Updates the Issue Requested Qty field in PO Line Items - add this requested qty to existing
' Creates Allocation records for all STOCK line items and update Allocated Qty field in Material _
  Code
' Updates the Allocated Material Qty field in the Material Code record
' Sets Posted flag, so this particular request cannot be added to even if the total PO Item(s) Qty _
    was (were) not requested
'*******************************************************************************************************

Dim Mydb As DAO.Database, MyQuery As QueryDef, alcnrs As DAO.Recordset, isreqrs As DAO.Recordset, matlsrs As DAO.Recordset, _
polirs As DAO.Recordset, grpdstkrs As DAO.Recordset

Set Mydb = DBEngine.Workspaces(0).Databases(0)

If Me![POSTED] = 0 Then

'* Post Request Line Item records

    Set MyQuery = Mydb.CreateQueryDef()
MyQuery.NAME = "FILL MATERIAL ISSUE REQUEST LINE ITEMS"
MyQuery.sql = "INSERT INTO [tblMaterialIssueRequestLineItems] SELECT[qryFillMaterialIssueRequestLineItems].* FROM [qryFillMaterialIssueRequestLineItems]"
Mydb.QueryDefs.Append MyQuery
MyQuery.Execute
MyQuery.CLOSE
Mydb.QueryDefs.Delete "FILL MATERIAL ISSUE REQUEST LINE ITEMS"

 sql = "[MATERIAL ISSUE REQUEST ID]=" & Forms![frmMaterialIssueRequests]![ID]
 
Set isrqrs = Mydb.OpenRecordset("SELECT* FROM [qryMaterialIssueRequestsUpdateData] WHERE" & sql & ";")

Set polirs = Mydb.OpenRecordset("PURCHASE ORDER LINE ITEMS")

isrqrs.MoveFirst
Do Until isrqrs.EOF
stkitm = isrqrs![STOCK ITEM]
matcode = isrqrs![MATERIAL CODE]
matishreqid = isrqrs![MATERIAL ISSUE REQUEST ID]
matishreqLIid = isrqrs![ID]
poliid = isrqrs![PURCHASE ORDER LINE ITEM ID]
reqqty = isrqrs![REQUESTED QTY]

' Update Requested Qty in PO Line Item record
polirs.FindFirst "[ID]=" & poliid
newqty = polirs![ISSUE REQUESTED QTY] + reqqty
polirs.Edit
polirs![ISSUE REQUESTED QTY] = newqty
polirs.Update

isrqrs.MoveNext

Loop
isrqrs.CLOSE
polirs.CLOSE


' Create allocation records and update Allocated Material Qty in Material Code if a Stock Item


 
Set grpdstkrs = Mydb.OpenRecordset("SELECT* FROM [qryMaterialIssueRequestGrpdStockItemQties] WHERE" _
& sql & ";")

If Not (grpdstkrs.BOF And grpdstkrs.EOF) Then

 grpdstkrs.MoveFirst
    
   
Set alcnrs = Mydb.OpenRecordset("tblStockAllocations")

Set matlsrs = Mydb.OpenRecordset("Materials")

Do Until grpdstkrs.EOF

matcode = grpdstkrs![MATERIAL CODE]
matishreqid = grpdstkrs![MATERIAL ISSUE REQUEST ID]
alctdqty = grpdstkrs![ALLOCATED QTY]

' Update Allocation Qty in Material Code

matlsrs.FindFirst "[MATERIAL CODE]='" & matcode & "'"
oldalctd = matlsrs![ALLOCATED MATERIAL QTY]
newqty = oldalctd + alctdqty
curstock = matlsrs![STOCK]
matlsrs.Edit
matlsrs![ALLOCATED MATERIAL QTY] = newqty
matlsrs.Update


alcnrs.AddNew
alcnrs![MATERIAL CODE] = matcode
alcnrs![MATERIAL ISSUE REQUEST ID] = matishreqid
alcnrs![OLD ALLOCATED] = oldalctd
alcnrs![ALLOCATED MATERIAL QTY] = alctdqty
alcnrs![TIME STAMP] = Now()
alcnrs![CURRENT USER] = CurrentUser()
alcnrs![CURRENT STOCK] = curstock
alcnrs.Update


grpdstkrs.MoveNext

Loop

alcnrs.CLOSE
matlsrs.CLOSE
grpdstkrs.CLOSE

Else
Set alcnrs = Mydb.OpenRecordset("tblStockAllocations")
alcnrs.AddNew
alcnrs![MATERIAL CODE] = matcode
alcnrs![MATERIAL ISSUE REQUEST ID] = matishreqid
alcnrs![OLD ALLOCATED] = oldalctd
alcnrs![ALLOCATED MATERIAL QTY] = 9999
alcnrs![TIME STAMP] = Now()
alcnrs![CURRENT USER] = CurrentUser()
alcnrs![CURRENT STOCK] = curstock
alcnrs.Update

alcnrs.CLOSE
End If



Me![POSTED] = -1

Else: mesij = MsgBox("Items already posted", vbOKOnly, "POSTED")

Is this related to SQL or another function maybe message box etc

EDIT: I have now fixed this, turns out it did not like this statement being on 2 Lines:

Set grpdstkrs = Mydb.OpenRecordset("SELECT* FROM [qryMaterialIssueRequestGrpdStockItemQties] WHERE" _
& sql & ";")

Is now:

Set grpdstkrs = Mydb.OpenRecordset("SELECT* FROM [qryMaterialIssueRequestGrpdStockItemQties] WHERE" & sql & ";")
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 15:04
Joined
Jan 14, 2017
Messages
18,186
Alex
You didn't add the space after SELECT.
You also need a space after WHERE towards the end of your code

I recommend you follow my other suggestion as well to remove the query def code.

The error message will be caused by you needing to add delimiters.
Identify when the error occurs by debugging that lengthy code sample

For info, older versions of Access were more tolerant of code errors than more recent versions
 

AlexDoran

Registered User.
Local time
Today, 15:04
Joined
Feb 25, 2014
Messages
20
Isladogs,

Thank you very much for your help. It is greatly appreciated - i will certainly look to remove those lines of code and follow your advice.

All of this corruption is pointing me towards trying to migrate to SQL.

Will this a considerably painful task?
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:04
Joined
Jan 23, 2006
Messages
15,364
All of this corruption is pointing me towards trying to migrate to SQL
.

What does this mean? Behind the query designer, queries are all sql. The graphic interface is an attempt to remove some complexity from the user ( a picture of the underlying SQL).

(you all probably think im lying
)
No. Converting from version to version, or major edits, or adapting someone else's code is open to typos, misunderstanding, shortcuts, learning new concepts and a number of other hidden artifacts -- nobody thinks you're lying.

Good luck with your project.
 

AlexDoran

Registered User.
Local time
Today, 15:04
Joined
Feb 25, 2014
Messages
20
Well i wanted to try and ditch the accdb Backend and import the data into sqlexpress or similar instead. I have done some reading on it today especially, but also in the past and i can never really tell if a whole redesign of the code on the front end would be required.

Thanks

Alex
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:04
Joined
Jan 23, 2006
Messages
15,364
OK so you SQL reference was not to SQL the language (or the MS Access variant).
You were referring to SQL Server Express the RDBMS software product.

Where exactly are you in the conversion project? mdb/2000 to accdb/2007
Is there a performance/size limitation on the database?
Is this an operational/production system?
What conversion approach are you using? parallel operation? redesign with new functionality?
multiple testing/testers ?
 

AlexDoran

Registered User.
Local time
Today, 15:04
Joined
Feb 25, 2014
Messages
20
In theory i have now finished converting from 2000 to 2007. Im sure maybe a few more aspects of the system will need tweaking or fixing, references adding as some of the dormant parts of it get used occasionally. This is a live production system - its roughly 20 years old. The backend is ~900mb (At the beginning of the year i purged many old records, it was closer to 2gb in size before i did this).

I did this conversion out of pure desperation today as the backend was corrupting 4 or 5 times a day, and i was not making any progress in identifying who or what was causing it, or if it is a combination of things.

Approximatley 30 users could be acessing the database at one time.

I wondered if porting the backend to SQL Server could improve performance and increase reliability, although i would be performing this on my own and doing the majoirty of the testing on it. I just need to educate myself on what would be required to make it happen.

Thanks

Alex
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:04
Joined
Oct 29, 2018
Messages
21,359
Approximatley 30 users could be acessing the database at one time.

I wondered if porting the backend to SQL Server could improve performance and increase reliability, although i would be performing this on my own and doing the majoirty of the testing on it. I just need to educate myself on what would be required to make it happen.

Thanks

Alex
Hi Alex. Migrating the data to SQL Server will definitely increase reliability but it won't necessarily improve performance automatically. You could improve performance by making sure your Access front end is designed to take advantage of the fact it's using SQL Server as the backend and minimize unnecessary trips back and forth to the server. Basically, the more processing you can pass on to the server generally means better performance. There are a few articles available on what things you need to watch out for when moving from Access to SQL Server. Good luck!
 

isladogs

MVP / VIP
Local time
Today, 15:04
Joined
Jan 14, 2017
Messages
18,186
I wrote this several hours sgo but forgot to post it.
Apologies if I'm repeating existing comments or if no longer relevant.

If you have already split the FE and BE, upsizing the BE to SQL Server can be a fairly trivial task unless you use unsupported data types such as MVFs or attachment fields. There should be no need to modify the FE at that stage.

The immediate advantages will be improved stability and security.
However you probably won't see any improvements in performance unless queries and SQL statements are redesigned so they are performed in the server. That is where redesign will come in in order to optimise the use of SQL Server
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:04
Joined
Feb 19, 2013
Messages
16,555
I would be concerned about why you are getting so much corruption. Moving the BE to SQL server may not resolve your users experience.

You have a split database which is good. The other reasons are usually due to users sharing the same front end or users connecting via a wireless connection. Do either of these scenario's apply to you?
 

Users who are viewing this thread

Top Bottom