Make View Problem 1

[RonRepp]

Hi all:

I'm trying to create a view using ADO and VB6 into an Access Database and I keep getting a "Syntax Error in Parameter Clause".

Here is the code:
[code>>ADO Code]
Sub MakeView(ByVal vName As String, ByVal sSQL As String)

Dim Fld As String
Dim i As Integer
Dim TName As String

TName = TV1.SelectedItem.Text

For i = 0 To ListBox2.ListCount - 1
If i = ListBox2.ListCount - 1 Then
Fld = Fld & "[" & TName & "].[" & ListBox2.List(i) & "]" & vbCrLf
Else
Fld = Fld & "[" & TName & "].[" & ListBox2.List(i) & "], " & vbCrLf
End If
Next

Dim Con As ADODB.Connection
Set Con = New ADODB.Connection
Con.ConnectionString = sCon
Con.Open sCon

Dim S, F As String
Dim Pos As Integer
Pos = InStrRev(vName, "\")
F = Mid(vName, Pos + 1, Len(vName))
SQL = Replace(sSQL, vbTab, " ")
SQL = Replace(sSQL, vbCrLf, " ")


S = "CREATE VIEW " & F & " (" & Fld & ") AS "
S = S & " " & SQL '& ")"
Debug.Print S
Con.Execute S
Con.Close
Set Con = Nothing


End Sub
[/code]

Here is how the string lays out:

[code>>Create View Statement]

CREATE VIEW TestView ([ImportedTable1].[ID],
[ImportedTable1].[Keywords],
[ImportedTable1].[Variables],
[ImportedTable1].[September],
[ImportedTable1].[Sept],
[ImportedTable1].[Annual Monthly Search],
[ImportedTable1].[Competition],
[ImportedTable1].[Global Return],
[ImportedTable1].[Sept Return],
[ImportedTable1].[Oct],
[ImportedTable1].[Nov],
[ImportedTable1].[Dec],
[ImportedTable1].[Jan],
[ImportedTable1].[Feb],
[ImportedTable1].[Mar],
[ImportedTable1].[Apr],
[ImportedTable1].[May],
[ImportedTable1].[Jun],
[ImportedTable1].[Jul],
[ImportedTable1].[Aug],
[ImportedTable1].[Sep]
) AS (SELECT [ImportedTable1].[ID], [ImportedTable1].[Keywords], [ImportedTable1].[Variables], [ImportedTable1].[September], [ImportedTable1].[Sept], [ImportedTable1].[Annual Monthly Search], [ImportedTable1].[Competition], [ImportedTable1].[Global Return], [ImportedTable1].[Sept Return], [ImportedTable1].[Oct], [ImportedTable1].[Nov], [ImportedTable1].[Dec], [ImportedTable1].[Jan], [ImportedTable1].[Feb], [ImportedTable1].[Mar], [ImportedTable1].[Apr], [ImportedTable1].[May], [ImportedTable1].[Jun], [ImportedTable1].[Jul], [ImportedTable1].[Aug], [ImportedTable1].[Sep] FROM ImportedTable1 WHERE [ImportedTable1].[ID] =4)
[/code]

Any help will be greatly appreciated.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[PWise]

The syntex for create view is

CREATE VIEW TestView 

As

SELECT   [ImportedTable1].[ID],      [ImportedTable1].[Keywords],    [ImportedTable1].[Variables],   [ImportedTable1].[September],   [ImportedTable1].[Sept],    [ImportedTable1].[Annual Monthly Search],   [ImportedTable1].[Competition],     [ImportedTable1].[Global Return],   [ImportedTable1].[Sept Return],     [ImportedTable1].[Oct],     [ImportedTable1].[Nov],     [ImportedTable1].[Dec],     [ImportedTable1].[Jan],     [ImportedTable1].[Feb],     [ImportedTable1].[Mar],     [ImportedTable1].[Apr],     [ImportedTable1].[May],     [ImportedTable1].[Jun],     [ImportedTable1].[Jul],     [ImportedTable1].[Aug],     [ImportedTable1].[Sep] FROM ImportedTable1 WHERE [ImportedTable1].[ID] =4

 

[RonRepp]

Thanks...a star for you.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net