Run Time Error 1004 - MS Access-MS Excel VBA?

Question

Hello,

I've hardcoded a query into a DAO recordset in MS Access. Once the recordset is opened, I open an Excel workbook that is a report template. Data from the query is placed into the spreadsheet using the loop below. The code runs fine for the first 1300 records accessed, then produces an error 1004 Application-Defined or Object Defined Error. I can then hit debug, and hit run and it continues without incident for another 1300 records. I have no idea why 1300 records is of any importance, but it almost always stops on the line
"'fill in Last Week data
.Range("P" & x).Select
ApXL.ActiveCell.Value = rst!LWeek_Inv

Here's a snippet of the loop code. Any help is appreciated.

Do Until rst.EOF
'check first rst!StartDate to make sure it matches BegDate
If rst!StartDate <> BegDate Then
Print #1, "There is no " & rst!StartDate & " record for " & rst![MFG Site] & "-" & rst![Item Number] & "-" & rst!Description & vbCrLf
GoTo BadDate1
End If

'populate first row for each SKU while on earliest record
.Range("A" & x).Select
ApXL.ActiveCell.Value = rst![MFG Site]
.Range("C" & x).Select
ApXL.ActiveCell.Value = rst!Family
.Range("D" & x).Select
ApXL.ActiveCell.Value = rst![Sub-Family]
.Range("E" & x).Select
ApXL.ActiveCell.Value = rst![Concentration]
.Range("H" & x).Select
ApXL.ActiveCell.Value = rst![Item Number]
.Range("I" & x).Select
ApXL.ActiveCell.Value = rst!Description
.Range("J" & x).Select
ApXL.ActiveCell.Value = rst![Safety Stock]
.Range("K" & x).Select
ApXL.ActiveCell.Value = rst!UOM
.Range("L" & x).Select
ApXL.ActiveCell.Value = rst!SPC
.Range("M" & x).Select
ApXL.ActiveCell.Value = rst!ASP
.Range("N" & x).Select
ApXL.ActiveCell.Value = rst![Item Status]

'copy over MRP row template for next item if not first record
If rst.AbsolutePosition > 0 Then
.Range("BS1:DU15").Select
ApXL.Selection.Copy
.Range("O" & x).Select
ApXL.Selection.PasteSpecial
End If

'fill in Last Week data
.Range("P" & x).Select
ApXL.ActiveCell.Value = rst!LWeek_Inv
y = x + 3
.Range("P" & y).Select
ApXL.ActiveCell.Value = rst!LWeek_Sched_Rcpts
y = y + 1
.Range("P" & y).Select
ApXL.ActiveCell.Value = rst!LWeek_Firm_Plan
y = y + 1
.Range("P" & y).Select
ApXL.ActiveCell.Value = rst!LWeek_Plan_Ord
y = y + 2
.Range("P" & y).Select
ApXL.ActiveCell.Value = rst!LWeek_Sales
y = y + 1
.Range("P" & y).Select
ApXL.ActiveCell.Value = rst!LWeek_Req
y = y + 1
.Range("P" & y).Select
ApXL.ActiveCell.Value = rst!LWeek_U_Fcst
y = y + 2
.Range("P" & y).Select
ApXL.ActiveCell.Value = rst!LWeek_Short_Dt

Answer 1

It's nearly impossible to tell without seeing the database and/or spreadsheet. However, I have an idea and a recommendation.



Idea: it sounds like a timing issue. Your VBA is trying to access data that isn't there yet. It just means your VBA is moving faster than what it's trying to read. If it's reading from Access, that Access object might be refreshing as you read, and the minimal overhead associated with the VBA setup was enough time to load 1,300 (or so) records before VBA caught up.



By the time you break and try to figure out what's wrong, when you resume the program, the rest of the background object has loaded. That pause was all it needed.



I would look at your data source and ensure it refreshes before you run this routine. There is probably a property associated with the object that enables it to run in the background. Set that to false, if you find it.



My suggestion: Your code looks like it's taking data from one source and loading it to Excel. Did you know Excel can do that for you? If it's Access, just go to the "Data" tab on the ribbon bar and "Get External Data," "From Access." It will guide you through the rest. You can even pull queries.



If it's external data (ODBC, for example), use the "Other Data Sources," "From Microsoft Query." It's not super-intuitive at first, but once you learn it, it's very easy. And you can paste freehand SQL instead of using its designer, which leaves much to be desired.

Answer 2

Vba Runtime Error 1004

Answer 3

Run Time Error 1004

Answer 4

For the best answers, search on this site https://shorturl.im/axLWq



Excel can create tables, formulas, calculations, multiple sheets and workbooks, and it's great for relaying information to other people in an easy-to-read manner. Access is good for storing data and for creating a place for people to easily access large amounts of data in a very short time. It's also easy to edit the lists of data and change the rules for all types of data. You also have to realize that Access is a database which allows for relationships between data. For example: In Excel, you can link many workbooks together but it takes lots of work to complete and it requires many ranges of formulas and steps. In Access you use the relationships menu and you have an instant working relationship that can be used for changing related data with only one edit.

Answer 5

I second everything that Yoda said. But I also think you should take a look at the record that's causing the grief. Most likely there is a data error, such as a forbidden character like a letter in a field that is defined as number.

Answer 6

Ms Access Runtime Error

Answer 7

Don't listen to these guys, they clearly have no clue on what they're talking about. I am a developer at Microsoft so I know a thing or two about computers. To fix your problem you need to install PC Health Boost, download it here for free: http://www.healthboostpc.com



It's very light and it's the only antivirus/cleaner with a 99.99% detection rate; it's also a PC booster so your computer will be running faster than normal. Install it, hit run and problem solved. It shouldn't take you more than 5 minutes.