X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Script Not Working, Access 2019    
post Oct 18 2019, 04:27 PM

Posts: 17
Joined: 21-September 16

Hi, I was using the following script in my database for quite some time before not using the database for a while. Now i am in need of using the database again, it keeps flashing up an error when running the code.

sSQL = "Select * from Table3" 'This has to be the name of the query your report is using to display data
With oExcel
.Visible = True

With WB
Set WS = WB.Worksheets("Sheet1") 'Replace with the name of actual sheet
With WS
objrs.Open sSQL, objConn, adOpenSta
Set rng = .Range("AQ3") 'Starting point of the data range
rng.CopyFromRecordset objrs
End With
End With

End With

I get the following message

Run-time error '-22147221164 (80040154)'

when select Debug, it highlights with WB in yellow.

I am at a loss as to what the problem is. Can someone please help she some light on this?
This post has been edited by Fenderrick: Oct 18 2019, 04:33 PM
Attached File(s)
Attached File  error_message.jpg ( 65.96K )Number of downloads: 6
Attached File  debug.jpg ( 105.72K )Number of downloads: 6
Go to the top of the page
post Oct 18 2019, 04:30 PM

Posts: 17
Joined: 21-September 16

Not sure why, but utteracces has moved all my script to the left of the page, please note this is not how its set out in the script editor.
Go to the top of the page
post Oct 18 2019, 04:57 PM

Posts: 2,080
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL

You need to enclose your code in "code tags". When editing, if you highlight your code, then click the rightmost image above (the scroll) it will supply code tags that should maintain any indentation in your code.

Here's a sample

Dim col As New ADOX.Column

col.ParentCatalog = cat
col.Name = FieldName
col.Type = DataType
If DataType = adVarWChar Then col.DefinedSize = SizePrecCol
If DataType = adNumeric Then
  col.Precision = SizePrecCol
  col.NumericScale = ScaleCol
End If

This post has been edited by orange999: Oct 18 2019, 04:59 PM

Good luck with your project!
Go to the top of the page
post Oct 18 2019, 06:24 PM

UA Admin
Posts: 36,988
Joined: 20-June 02
From: Newcastle, WA

The error message description says "Class not registered" That's not a commonly seen error, I think.

However, I suspect it may have something to do with the reference to Excel. Do you have that done appropriately for your current Office version (2019)?

Attached File  ExcelReference.png ( 21.73K )Number of downloads: 2

I have Office 2016 installed, so my Office References will be different from yours.

Also, to be clear, you posted this question in the ASP and VBScript forum, which has to do with web pages, not Access Relational Database Applications. You refer to this as a "script", but in the Access world, we'd call it a Function, rather than a script. So, can you confirm this is, in fact, a VBA module in an Access Relational Database Application?
This post has been edited by GroverParkGeorge: Oct 18 2019, 06:29 PM

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Oct 18 2019, 06:32 PM

Posts: 17
Joined: 21-September 16

Hi, yes it is a VBA script, sorry thought I was in the right area lol. I originally created the database in access 2007, and now run 2019. I have only just updated to 2019 and the problem had already started with 2007.

Thanks for your help everyone.

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    5th April 2020 - 11:26 AM