Converting Access SQL to SQL 2000 
Author Message
 Converting Access SQL to SQL 2000

I cannot get the following Sql query to run in SQL 2000
It works fine in Access 2002.
Any Help is appreaciated

Heath Johnson

Public Sub EngLogFill()
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command

Dim StrSql, criteria, Team As String
Dim x, ctr As Variant
Dim Index As Integer

Team = "'" & frmQuoteSearch.cboCustomer(8).Text & "'"
If Team = "'All Teams'" Then
Team = """%"""
End If

StrSql = "SELECT TblProcessor.Team,
tblApprovalDwgLog.LogDate,
tblApprovalDwgLog.IFSProjectNumber," _
& " tblMain.ProjectName, tblMain.Processor,
tblMain.CarType, tblApprovalDwgLog.ArchPrints,
tblApprovalDwgLog.ArchPrintsBin," _
& " tblApprovalDwgLog.WantLayoutDWG,
tblApprovalDwgLog.LayoutEngName," _
& " tblApprovalDwgLog.LayoutDwgDateComplete," _
& " tblApprovalDwgLog.CabEngName,
tblApprovalDwgLog.CabDwgDateComplete " _
& " FROM (tblMain INNER JOIN tblApprovalDwgLog ON
tblMain.IFSProjectNumber =
tblApprovalDwgLog.IFSProjectNumber) INNER JOIN
TblProcessor ON tblMain.Processor = TblProcessor.Processor
where Completed = False and TblProcessor.Team Like " &
Team & " ORDER BY TblProcessor.Team, LogDate"

conn.Open "Provider=sqloledb;Server=Mnelv2;Initial
Catalog=Project Info ToolSQL;User Id=sa;Password=;"
Set cmd.ActiveConnection = conn
cmd.CommandText = StrSql
rs.Open cmd, , adOpenStatic, adLockOptimistic

Set frmQuoteSearch.MSHFGEngLog(0).DataSource = rs
frmQuoteSearch.MSHFGEngLog(0).Refresh

End Sub



Sun, 22 May 2005 05:29:12 GMT  
 Converting Access SQL to SQL 2000
do you get any error message? there's nothing there that I can see would
cause problems, though it's rather verbose for my tastes.

________________________________________
I got bored with my old signature,
so I changed it

Atrax. MVP, IIS
http://rtfm.atrax.co.uk/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 22 May 2005 11:51:12 GMT  
 Converting Access SQL to SQL 2000
Heath,

First of all remove all paranthases in INER JOIN. Do you get any error or
just unexpected result?

--
Val Mazur
Microsoft MVP


Quote:
> I cannot get the following Sql query to run in SQL 2000
> It works fine in Access 2002.
> Any Help is appreaciated

> Heath Johnson

> Public Sub EngLogFill()
> Set conn = New ADODB.Connection
> Set rs = New ADODB.Recordset
> Set cmd = New ADODB.Command

> Dim StrSql, criteria, Team As String
> Dim x, ctr As Variant
> Dim Index As Integer

> Team = "'" & frmQuoteSearch.cboCustomer(8).Text & "'"
> If Team = "'All Teams'" Then
> Team = """%"""
> End If

> StrSql = "SELECT TblProcessor.Team,
> tblApprovalDwgLog.LogDate,
> tblApprovalDwgLog.IFSProjectNumber," _
> & " tblMain.ProjectName, tblMain.Processor,
> tblMain.CarType, tblApprovalDwgLog.ArchPrints,
> tblApprovalDwgLog.ArchPrintsBin," _
> & " tblApprovalDwgLog.WantLayoutDWG,
> tblApprovalDwgLog.LayoutEngName," _
> & " tblApprovalDwgLog.LayoutDwgDateComplete," _
> & " tblApprovalDwgLog.CabEngName,
> tblApprovalDwgLog.CabDwgDateComplete " _
> & " FROM (tblMain INNER JOIN tblApprovalDwgLog ON
> tblMain.IFSProjectNumber =
> tblApprovalDwgLog.IFSProjectNumber) INNER JOIN
> TblProcessor ON tblMain.Processor = TblProcessor.Processor
> where Completed = False and TblProcessor.Team Like " &
> Team & " ORDER BY TblProcessor.Team, LogDate"

> conn.Open "Provider=sqloledb;Server=Mnelv2;Initial
> Catalog=Project Info ToolSQL;User Id=sa;Password=;"
> Set cmd.ActiveConnection = conn
> cmd.CommandText = StrSql
> rs.Open cmd, , adOpenStatic, adLockOptimistic

> Set frmQuoteSearch.MSHFGEngLog(0).DataSource = rs
> frmQuoteSearch.MSHFGEngLog(0).Refresh

> End Sub



Sun, 22 May 2005 11:59:11 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Converting program from SQL 2000 to Access 2000

2. Access 2000 VBA/SQL - cannot create pseudo-index on linked SQL Server table

3. Accessing reports in Access 2000 with SQL 2000 as Backend

4. Executing MS SQL Server 2000 stored procs via MS Access 2000

5. HELP!!..accessing access 2000 with linked SQL Server tables

6. doing updating from grid to sql server with stored procedure sql 2000

7. SQL 7 & SQL 2000

8. SQL Connectivity - CR9 Developer Edition, VB6, SQL Server 2000

9. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL

10. BUG: Visual Basic 6.0 T-SQL Debugger Fails Against SQL Server 2000 Named Instance (Q310647)

11. SQL 7 and SQL 2000 server

12. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL Syntax

 

 
Powered by phpBB® Forum Software