[X]My Assistant

[Fenderrick]

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
objrs.Close
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)

 error_message.jpg ( 65.96K )Number of downloads: 6
 debug.jpg ( 105.72K )Number of downloads: 5

 

[Fenderrick]

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.

[orange999]

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

CODE

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

[GroverParkGeorge]

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)?

 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

[Fenderrick]

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.

Pete