How to fix a run-time error in ADO process?

[feipezi]

Hello,

I need you guys to help me with some ADO messages. I have a snapshot attached. I do not understand what's wrong with the name of the table. I ran a few procedures with very similar table name without any issues until now.

you can see the SQL statement in the query cannot be simpler than this:



Select distinct mkt,product from [mktprod$] where mkt in ('BAB')


Thanks in advance.

 

[SkipVought]

Hi

Select distinct mkt, product from [mktprod$] where mkt in ('BAB')

Does this SQL run without error in, say, MS Query or Access?

I assume that you have a sheet named mktprod with headings in row 1, among which are mkt and product.

If both answers are "yes" then
1) post the error message and
2) post all your code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[feipezi]

The errors are already sent in the snapshot previously.
Here is the code:


Sub Submit_MKT()
Application.ScreenUpdating = False
Dim jqzConnect As String
Dim jqzRecordset As ADODB.Recordset
Dim jqzTable As ADODB.Recordset
Dim jqzSQL As String

jqzConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Users\pl04512\Documents\vba\Adotest5.xlsm;" & _
"Extended Properties=Excel 12.0"

jqzSQL = Sheet3.Range("g12").Value

' Sheet5.Cells(2, 1).CurrentRegion.Offset(1).Clear

Set jqzRecordset = New ADODB.Recordset
jqzRecordset.Open jqzSQL, jqzConnect, adOpenStatic, adLockReadOnly

Set jqzTable = New ADODB.Recordset
jqzTable.Open jqzSQL, jqzConnect
Sheet8.Cells(2, 14).CopyFromRecordset jqzTable

Sheet8.Activate
Cells(1, 14).Activate
Application.ScreenUpdating = True
End Sub


jqzSQL = Sheet3.Range("g12").Value:
Select distinct mkt,product from [mktprod$] where mkt in ('BAB')
When debugging, the SQL will be highlighted showing the issues are in the Select statements.

Please let me know more info needed.
thanks.

 

[strongm]

>The errors are already sent in the snapshot previously.

Er ... no sign of a snapshot that I can see.

 

[feipezi]

let me try again. Sorry about that.
Please let me know if still not available.

 

[SkipVought]

Why this???

Set jqzTable = New ADODB.Recordset
jqzTable.Open jqzSQL, jqzConnect
Sheet8.Cells(2, 14).CopyFromRecordset jqzTable

Why not this???

Set jqzRecordset = New ADODB.Recordset
jqzRecordset.Open jqzSQL, jqzConnect, adOpenStatic, adLockReadOnly

Sheet8.Cells(2, 14).CopyFromRecordset [b]jqzRecordset[/b]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[feipezi]

Hi, I replaced the part of the code by your code and ran it. But I got the exactly the same error message.
thanks.

 

[SkipVought]

What error message?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

[pre]Sheet8.Activate
Cells(1, 14).Activate[/pre]
There is a difference between Select and Activate for range (the second line above, see

https://docs.microsoft.com/en-us/office/vba/api/excel.range.activate).

combo

 

[feipezi]

Good morning Skip, the error message is the same as before. It sounds like you didn't see the snapshot that I uploaded (as I uploaded, it was a success). Anyway, the error goes like this:

Run-time error: '-2147467259 (80004005)'
'mktprod$' is not a valid name, make sure that it does not include invalid characters or punctuation and that it is not too long

That's it.

Hello Combo, it doesn't look like the destination cell has much to do with the error message. Excel won't recognize the tab name: mktprod. But let me try your suggestion too.
will keep you posted.
thank you both.

 

[combo]

I agree. Activating cell fails only if it is not in active sheet, otherwise it's ok. However, you may consider if activating is necessary, most code works without it, you need only full referencing.
As you work with sheet code name (as in [tt]Sheet8.Cells(2, 14).CopyFromRecordset jqzTable[/tt]), you may consider to change it to more meaningful, the (Name) property in properties window.

combo

 

[strongm]

> you didn't see the snapshot that I uploaded

None of us are seeing it

 

[feipezi]

Sorry to hear it. I saw something like 'upload success' before I submit the post.
Anyway, here is the error message again, which is what the snapshot was for:

Run-time error: '-2147467259 (80004005)'
'mktprod$' is not a valid name, make sure that it does not include invalid characters or punctuation and that it is not too long

That's it.
Thanks.

 

[SkipVought]

[tt]mktprod[/tt] should be the sheet name with no leading or trailing spaces.

Is that a fact?


That's the issue. If not, then please upload your workbook for further inspection.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

Like strongm said, none of us are seeing your snapshot(s).
Please, use Preview button before submitting your posts along with TGML tags to format your code. Use [tt]CODE[/tt] instead of BOLD


---- Andy

There is a great need for a sarcasm font.

 

[feipezi]

Hello guys,
ADO is driving me crazy! Besides ADO, do you recommend something else that does the similar as ADO but relatively easier to handle. Forget about Advanced Filter that I dislike.
Let me tell you about what I really want.
I have a table with over 50,000 records, where we have manufacturer, product and market in pharmaceutical industry.
I set up a couple of Listboxes. One for the producers; the other for market. Now if I select one of the producers from the 1st box, the 2nd box will show the markets ONLY for that particular producer; the next step is that pick one of the markets, the coming Listbox will show the products ONLY for the market selected. But 'Boom', the job failed getting the error msg shown above as I was trying to get products. Not sure if I made myself clear but I tried.
Thanks again.

 

[SkipVought]

We are 'talking' past each other.

You are not addressing the questions that we raised as a result of previous information.

Please address our questions!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[feipezi]

Hello Skip,
Yes. the name of the tab is 'mktprod' without any leading or trailing blanks.
I cannot send you the whole workbook but I can cut it down and make it deliverable.
thanks.

 

[SkipVought]

Good. Just need the mktprod sheet with just a few rows and your VBA in a workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

> I can cut it down and make it deliverable.

That would be great.