I am using SQL to query an Access DB and dump the recordset into
Excel. I use this query against different tables within the DB and
vary the FROM parameter and SELECT parameters according to the table
name and corresponding fields in the table. I have used this for
several different queries without a problem. Now, I am going against
a new table but having a problem with a field called "SIZE". I can't
figure out why this is a problem but if it's set as a parameter in the
SELECT statement I get a runtime error. It's not a spelling issue,
hard to spell this word wrong but I checked anyway, so that isn't the
problem. The only thing I can think of is that SIZE is a restricted
word that's used for something else. Anybody have any suggestions as
to what to check?

Thanks

I checked SQL Server Books Online and "SIZE" is not a
reserved keyword. If you could post the actual SQL query
it might help pinpoint the problem.


-----Original Message-----
I am using SQL to query an Access DB and dump the
recordset into
Excel. I use this query against different tables within
the DB and
vary the FROM parameter and SELECT parameters according
to the table
name and corresponding fields in the table. I have
used this for
several different queries without a problem. Now, I am
going against
a new table but having a problem with a field
called "SIZE". I can't
figure out why this is a problem but if it's set as a
parameter in the
SELECT statement I get a runtime error. It's not a
spelling issue,
hard to spell this word wrong but I checked anyway, so
that isn't the
problem. The only thing I can think of is that SIZE is a
restricted
word that's used for something else. Anybody have any
suggestions as
to what to check?

Thanks
.

The OP stated MS Access, not SQL Server. Anyhow, this is an Excel
forum so the chances are the OP is using MS Query. And 'SIZE' is a
reserved word in MS Query (but not MS Access):

Microsoft Knowledge Base Article - 125948
Reserved Keywords in MS Query
http://support.microsoft.com/default...b;en-us;125948

--

"rayd8" wrote in message ...
I checked SQL Server Books Online and "SIZE" is not a
reserved keyword. If you could post the actual SQL query
it might help pinpoint the problem.


-----Original Message-----
I am using SQL to query an Access DB and dump the
recordset into
Excel. I use this query against different tables within
the DB and
vary the FROM parameter and SELECT parameters according
to the table
name and corresponding fields in the table. I have
used this for
several different queries without a problem. Now, I am
going against
a new table but having a problem with a field
called "SIZE". I can't
figure out why this is a problem but if it's set as a
parameter in the
SELECT statement I get a runtime error. It's not a
spelling issue,
hard to spell this word wrong but I checked anyway, so
that isn't the
problem. The only thing I can think of is that SIZE is a
restricted
word that's used for something else. Anybody have any
suggestions as
to what to check?

Thanks
.

While 'SIZE' isn't a reserved word in MS Access, it is a Jet reserved word:

Microsoft Knowledge Base Article - 321266
Microsoft Jet 4.0 Reserved Words
http://support.microsoft.com/default...b;en-us;321266

Microsoft Knowledge Base Article - 109312
Reserved Words in Microsoft Access
http://support.microsoft.com/default...b;en-us;109312

--

"rayd8" wrote in message ...
I checked SQL Server Books Online and "SIZE" is not a
reserved keyword. If you could post the actual SQL query
it might help pinpoint the problem.


-----Original Message-----
I am using SQL to query an Access DB and dump the
recordset into
Excel. I use this query against different tables within
the DB and
vary the FROM parameter and SELECT parameters according
to the table
name and corresponding fields in the table. I have
used this for
several different queries without a problem. Now, I am
going against
a new table but having a problem with a field
called "SIZE". I can't
figure out why this is a problem but if it's set as a
parameter in the
SELECT statement I get a runtime error. It's not a
spelling issue,
hard to spell this word wrong but I checked anyway, so
that isn't the
problem. The only thing I can think of is that SIZE is a
restricted
word that's used for something else. Anybody have any
suggestions as
to what to check?

Thanks
.

Here's the code. Right now it doesn't have SIZE in the SELECT. When
it's in there I get a runtime error: 'Method 'Open' of
object'_'Recordset' failed.

Regarding the other responses to this thread:
What does OP an abbreviation for?
Are we really using MS Query here or is this SQL in an ADO wrapper?
I'm a novice here so any info on where to learn more about this (so I
can speak intelligently) would be appreciated.

Thanks



Public Const gszConnection As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=R:\main.mdb;"

Public Sub PlainTextQuery()

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String



'Create the connection string
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=R:\main.mdb;"

'Create the SQL Statement
szSQL = " SELECT ID, Property_Name, Year_Built, Occupancy,
Effective_Rent, Load_Factor, Concession, Alteration_Costs_Renewals,
Alter_Costs_New_Releases, Pro_Rata_Charges, Escalators,
Pct_Brokerage_Renewals, Pct_Brokerage_New_Leases " & _
" FROM rentoffc " & _
" WHERE (ID = 11504 OR ID = 11337 OR ID = 10808 OR ID = 11571
OR ID = 11568 OR ID = 11569 OR ID = 11632 OR ID = 11572)"


'Create the Recordset Object and run the query
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adCmdText

'Make sure we got records back
If Not rsData.EOF Then
'Dump the contents of the recordset onto the worksheet.
Sheet1.Range("A2:Z2").CopyFromRecordset rsData
'Close the recordset
rsData.Close
'Add headers to the worksheet.
With Sheet1.Range("A1:Z1")
.Value = Array("ID", "Property Name", "YearBlt", "Occ",
"EffRent", "Load", "Concession", "TI Renew", "TI New", "ProRata",
"Escalator", "Broker Renew", "Broker New", "SF", "QRent")
.Font.Bold = True
End With
'Fit the column width to the data
Sheet1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error; No records returned.", vbCritical
End If

'Close the recordset if still open.
If CBool(rsData.State And adStateOpen) Then rsData.Close
Set rsData = Nothing

End Sub

I am also a novice and would like some clarification.
Does Access and MS Query have their own variation of the
SQL language? The book I'm reading indicates that you can
input T-SQL language directly into MS Query (or VBA code)
to access data in an Access or SQL Server database.
-----Original Message-----
Here's the code. Right now it doesn't have SIZE in the
SELECT. When
it's in there I get a runtime error: 'Method 'Open' of
object'_'Recordset' failed.

Regarding the other responses to this thread:
What does OP an abbreviation for?
Are we really using MS Query here or is this SQL in an
ADO wrapper?
I'm a novice here so any info on where to learn more
about this (so I
can speak intelligently) would be appreciated.

Thanks



Public Const gszConnection As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=R:\main.mdb;"

Public Sub PlainTextQuery()

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String



'Create the connection string
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=R:\main.mdb;"

'Create the SQL Statement
szSQL = " SELECT ID, Property_Name, Year_Built,
Occupancy,
Effective_Rent, Load_Factor, Concession,
Alteration_Costs_Renewals,
Alter_Costs_New_Releases, Pro_Rata_Charges, Escalators,
Pct_Brokerage_Renewals, Pct_Brokerage_New_Leases " & _
" FROM rentoffc " & _
" WHERE (ID = 11504 OR ID = 11337 OR ID = 10808
OR ID = 11571
OR ID = 11568 OR ID = 11569 OR ID = 11632 OR ID = 11572)"


'Create the Recordset Object and run the query
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adCmdText

'Make sure we got records back
If Not rsData.EOF Then
'Dump the contents of the recordset onto the
worksheet.
Sheet1.Range("A2:Z2").CopyFromRecordset rsData
'Close the recordset
rsData.Close
'Add headers to the worksheet.
With Sheet1.Range("A1:Z1")
.Value = Array("ID", "Property
Name", "YearBlt", "Occ",
"EffRent", "Load", "Concession", "TI Renew", "TI
New", "ProRata",
"Escalator", "Broker Renew", "Broker New", "SF", "QRent")
.Font.Bold = True
End With
'Fit the column width to the data
Sheet1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error; No records returned.", vbCritical
End If

'Close the recordset if still open.
If CBool(rsData.State And adStateOpen) Then
rsData.Close
Set rsData = Nothing

End Sub



.

Sorry for mentioning MS Query, it is irrelevant to this problem. Just
after accusing someone of not reading the OP, I fail to spot the
mention of a recordset, ruling out MS Query!

I think the problem here is that SIZE is a reserved word in Jet.

ADO is the data access technology being used in this case to
communicate with the data engine. MS Query is an alternative data
access tool and isn't involved in this case. Jet is the database
engine, which does all the work of retrieving the data from the .mdb
file. MS Access is a GUI front end tool for designing Jet databases
and building forms and reports with controls bound to a Jet data
source (yuk!) and also isn't used in this case.

--

"rayd8" wrote in message ...
I am also a novice and would like some clarification.
Does Access and MS Query have their own variation of the
SQL language? The book I'm reading indicates that you can
input T-SQL language directly into MS Query (or VBA code)
to access data in an Access or SQL Server database.
-----Original Message-----
Here's the code. Right now it doesn't have SIZE in the
SELECT. When
it's in there I get a runtime error: 'Method 'Open' of
object'_'Recordset' failed.

Regarding the other responses to this thread:
What does OP an abbreviation for?
Are we really using MS Query here or is this SQL in an
ADO wrapper?
I'm a novice here so any info on where to learn more
about this (so I
can speak intelligently) would be appreciated.

Thanks



Public Const gszConnection As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=R:\main.mdb;"

Public Sub PlainTextQuery()

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String



'Create the connection string
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=R:\main.mdb;"

'Create the SQL Statement
szSQL = " SELECT ID, Property_Name, Year_Built,
Occupancy,
Effective_Rent, Load_Factor, Concession,
Alteration_Costs_Renewals,
Alter_Costs_New_Releases, Pro_Rata_Charges, Escalators,
Pct_Brokerage_Renewals, Pct_Brokerage_New_Leases " & _
" FROM rentoffc " & _
" WHERE (ID = 11504 OR ID = 11337 OR ID = 10808
OR ID = 11571
OR ID = 11568 OR ID = 11569 OR ID = 11632 OR ID = 11572)"


'Create the Recordset Object and run the query
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adCmdText

'Make sure we got records back
If Not rsData.EOF Then
'Dump the contents of the recordset onto the
worksheet.
Sheet1.Range("A2:Z2").CopyFromRecordset rsData
'Close the recordset
rsData.Close
'Add headers to the worksheet.
With Sheet1.Range("A1:Z1")
.Value = Array("ID", "Property
Name", "YearBlt", "Occ",
"EffRent", "Load", "Concession", "TI Renew", "TI
New", "ProRata",
"Escalator", "Broker Renew", "Broker New", "SF", "QRent")
.Font.Bold = True
End With
'Fit the column width to the data
Sheet1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error; No records returned.", vbCritical
End If

'Close the recordset if still open.
If CBool(rsData.State And adStateOpen) Then
rsData.Close
Set rsData = Nothing

End Sub



.

Thanks for your input. I agree, the problem is the Jet reserved word.
In developing a different query from the same DB I ran into a similar
problem with a field named QUOTED_STREET_RENT/SF. My guess is that
the presence of the "/" character is the problem. Any thoughts on
this?

BTW, someone else designed the DB. I'm not really familiar with Jet
and need a list of restricted words. Know where I can get this info?


On 27 Feb 2004 01:49:17 -0800, (onedaywhen)
wrote:

Sorry for mentioning MS Query, it is irrelevant to this problem. Just
after accusing someone of not reading the OP, I fail to spot the
mention of a recordset, ruling out MS Query!

I think the problem here is that SIZE is a reserved word in Jet.

ADO is the data access technology being used in this case to
communicate with the data engine. MS Query is an alternative data
access tool and isn't involved in this case. Jet is the database
engine, which does all the work of retrieving the data from the .mdb
file. MS Access is a GUI front end tool for designing Jet databases
and building forms and reports with controls bound to a Jet data
source (yuk!) and also isn't used in this case.

Microsoft Knowledge Base Article - 321266
Microsoft Jet 4.0 Reserved Words
http://support.microsoft.com/default...b;en-us;321266

--

PvtMadness wrote in message . ..
Thanks for your input. I agree, the problem is the Jet reserved word.
In developing a different query from the same DB I ran into a similar
problem with a field named QUOTED_STREET_RENT/SF. My guess is that
the presence of the "/" character is the problem. Any thoughts on
this?

BTW, someone else designed the DB. I'm not really familiar with Jet
and need a list of restricted words. Know where I can get this info?


On 27 Feb 2004 01:49:17 -0800, (onedaywhen)
wrote:

Sorry for mentioning MS Query, it is irrelevant to this problem. Just
after accusing someone of not reading the OP, I fail to spot the
mention of a recordset, ruling out MS Query!

I think the problem here is that SIZE is a reserved word in Jet.

ADO is the data access technology being used in this case to
communicate with the data engine. MS Query is an alternative data
access tool and isn't involved in this case. Jet is the database
engine, which does all the work of retrieving the data from the .mdb
file. MS Access is a GUI front end tool for designing Jet databases
and building forms and reports with controls bound to a Jet data
source (yuk!) and also isn't used in this case.