circular reference caused by alias in query definition

[f0rg3tfu1]

Hi all,

Not a programmer but am getting the "circular reference error" on a spreadsheet that I inherited. I am running the macro our former employee created, and I am getting:

"RunTime error 2147467259 (80004005)':
Circular Reference caused by alias 'Fiscal Year' in query definitions SELECT list."

So I tried to fix this by adding the fully qualified name, BECK MONTY.[Fiscal Year], but no luck.

Anyone know how to fix this Alias error? The Debug is taking me to line 9:

Sub BringInfo()


Dim Connection As ADODB.Connection
Dim rs As New ADODB.Recordset
Set Connection = New ADODB.Connection
detailsSource = ThisWorkbook.Path & "\" & ThisWorkbook.Name
Connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & detailsSource & ";Extended Properties=""Excel 12.0 Macro;HDR=YES;"""

LastRow = getlastrow(ActiveSheet.Name, "A")


Query = "select[Period],val([Fiscal Year]) as [Fiscal Year],[Journal Cd],[Name],[Project Name],[Transaction Desc],[Amount],abs([Amount]),[Voucher Number],[Invoice ID],[Project ID] from [GLDetail$] where [Project ID]='" & Range("B1").Value & "'"
rs.Open Query, Connection
Range("A" & LastRow + 1).CopyFromRecordset rs
rs.Close


End Sub

 

[strongm]

The context pf your question suggests that you are using Excel, in which case the language you are using is VBA rather than VB. Despite their similarities they are seperate langauges, and so you'd be better off asking in the dedicated VBA forum: forum707

That being said, the error is probably

val([Fiscal Year]) as [Fiscal Year]

where you are aliasing a calculated field to the name of an existing field and, worse, that existing field is the one the calculation is based on - hence the circular reference warning

Try changing

[tt]Query = "select[Period],val([Fiscal Year]) as [Fiscal Year],[Journal Cd],[Name],[Project Name],[Transaction Desc],[Amount],abs([Amount]),[Voucher Number],[Invoice ID],[Project ID] from [GLDetail$] where [Project ID]='" & Range("B1").Value & "'"
rs.Open Query, Connection[/tt]

to

[tt]Query = "select[Period],val([Fiscal Year]) as [Fiscal_Year],[Journal Cd],[Name],[Project Name],[Transaction Desc],[Amount],abs([Amount]),[Voucher Number],[Invoice ID],[Project ID] from [GLDetail$] where [Project ID]='" & Range("B1").Value & "'"
rs.Open Query, Connection[/tt]

 

[f0rg3tfu1]

Strongm,

Thanks so much, I tried changing the name and I also used your exact suggestion but I am still receiving an error:

"No value given for one or more required parameters."

I got this error over and over when I tried to change the name of the field to something completely different.

 

[strongm]

Yes, but it is a differentr error, so it looks like we fixed the first issue. And this newly revealed error message suggest you have a typo in at least one of your field names.

 

[combo]

[pre]..., [Transaction Desc], [Amount], abs([Amount]), [Voucher Number], ...[/pre]
You have no 'as' in the red marked part of SQL string.
I'm not sure if ADODB SQL understands 'val' and 'abs' functions. Make the query work without conversions and next try to add them one by one to isolate the issue.

combo

 

[strongm]

> I'm not sure if ADODB SQL understands 'val' and 'abs' functions

It does

The real error here is less a typo thjan the fact that TRANSACTION is a reserved word ... you probably want to change the column title in the excel sheet to something like [tt]TranactionDesc[/tt] rather than [tt]TransAction Desc[/tt] and update the query accordingly.

(I suspect that yu may then encounter another error ... but let's deal with this one first)

 

[f0rg3tfu1]

Well, the good news. I solved the issue... Looks like the referenced tab needed to have the headers in it and the person using the spreadsheet deleted the headers.

Either way, easy fix and thank god it wasn't much to deal with. Thanks for your help guys hopefully this wont happen again!

 

[strongm]

>the person using the spreadsheet deleted the headers

Yep, that would do it!