Hi,
I am trying to create a local cube populating data from a relational database like access,sql server or oracle.
In the eg.. mentioned below .. i hve created an MS Access DSN.

When I execute the code i am getting an error which says- Syntax error in column definition...

Incase you have come across this error or have any clues as to why it may be occuring kindly let me know ...

loydon11@rediffmail.com..
Thanks for sparing ur precious time.
######################################Code######## ##################

Private Sub cmdClick_Click()

Dim cnCube As ADODB.Connection
Dim s As String
Dim strProvider As String
Dim strDataSource As String
Dim strSourceDSN As String
Dim strSourceDSNSuffix As String
Dim strCreateCube As String
Dim strInsertInto As String
Dim strSource As String

On Error GoTo Error_cmdCreateCubeFromDatabase_Click

strSource = Connection(1)
strCreateCube = getCreateCube()
strInsertInto = getInsertInto()
Set cnCube = New ADODB.Connection

s = strSource & ";" & strCreateCube & ";" & strInsertInto & ";"

Screen.MousePointer = vbHourglass
cnCube.Open s

Screen.MousePointer = vbDefault
Exit Sub
Error_cmdCreateCubeFromDatabase_Click:
Screen.MousePointer = vbDefault
MsgBox Err.Description
If Err.Number <> 0 Then
msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
End If

End Sub
Private Function Connection(idbtype As Long) As String
'''''''''''''''''''''''''Database Connection to the Cube''''''''''''''''''
strProvider = "PROVIDER=MSOLAP"
strDataSource = "DATA SOURCE=c:\OlapCube.cub"
strSourceDSN = "SOURCE_DSN=AccessLoyd"
Connection = strProvider & ";" & strDataSource & ";" & strSourceDSN
End Function
Private Function getCreateCube() As String

''''''''''''''''''''''''''Create cube Structure'''''''''''''''''''''''''''
Dim strCreateCube As String
strCreateCube = "CREATECUBE=CREATE CUBE cbia( "
strCreateCube = strCreateCube & "DIMENSION[SSTORE],"
strCreateCube = strCreateCube & "LEVEL[SSTORE Name],"
strCreateCube = strCreateCube & "MEASURE [SSTORE Sales] "
strCreateCube = strCreateCube & "Function Sum "
strCreateCube = strCreateCube & "Format '#.#')"
getCreateCube = strCreateCube
End Function
Private Function getInsertInto() As String
'''''''''''''''''''''''''''Insert Clause and Select Clause'''''''''''''''''

Dim strInsertInto As String
'''''''''''''''''''''''''''Table name.Column Name''''''''''''''''''''''''''
strInsertInto = strInsertInto & "INSERTINTO=INSERT INTO cbia( SSTORE.[STORE Name],"
strInsertInto = strInsertInto & "Measures.[STORE Sales])"
strInsertInto = strInsertInto & "SELECT SSTORE.STORE_NAME AS COL1,"
strInsertInto = strInsertInto & "SSTORE.STORE_SALES AS COL2"
strInsertInto = strInsertInto & "FROM [SSTORE]"
getInsertInto = strInsertInto
End Function