collating sequence not supported 
Author Message
 collating sequence not supported

Hi,

I have been using VB6 to connect to an Access database using ADO with no
problems. I then tried experimenting by trying to connect to Excel. The
scenario is this:

Once the connection is made I have a button that should count the number of
records. If connected to an Excel file then when this button is pressed an
error is trapped which returns the message;

[Microsoft][ODBC Excel Driver] Selected collating sequence not supported by
operating systyem

If I then press the button again it returns the count correctly. Similarly
if I first select an Access data source, do a count, and then change the
data source to Excel, everything works fine first time.

Can anybody point me in the right direction or suggest where I am going
wrong?

The function I use to build the connection string is given below. The Excel
bit looks terribly ugly - I just got it from looking at what UDL comes up
with when connecting to Excel. A neater connection string for Excel would be
appreciated

----------------------------------------------------------------------------
-------------------
Public Function myConnectionString(xdatabasename As String) As String

        Dim strTemp1 As String

        If LCase(Right(xdatabasename, 4)) = ".mdb" Then
            myConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
            myConnectionString = myConnectionString & "; Data Source=" &
xdatabasename

        ElseIf LCase(Right(xdatabasename, 4)) = ".xls" Then

            myConnectionString = "Provider=MSDASQL.1"
            myConnectionString = myConnectionString & ";Persist Security
Info=False"
            strTemp1 = "DSN=Excel Files;DBQ=" & xdatabasename &
";DefaultDir=c:;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
            strTemp1 = """" & strTemp1 & """"
            myConnectionString = myConnectionString & ";Extended
Properties=" & strTemp1

        ElseIf LCase(Right(xdatabasename, 4)) = ".udl" Then
            myConnectionString = "File Name=" & xdatabasename

        Else
            xdatabasename = ""
        End If

       ' Debug.Print myConnectionString

End Function
----------------------------------------------------------------------------
----------------------------------------



Tue, 12 Aug 2003 06:27:23 GMT  
 collating sequence not supported
This is example that I found on Microsft site.

'   The following .CommandText causes the error, but only the first time it
is run within the ide.
'   cmd.CommandText = "Select F1, F2 from ""Sheet1$"""
'   The following .CommandText will not cause the error.
    'cmd.CommandText = "Select * from ""Sheet1$"" "

They are working on it.


Quote:
> Hi,

> I have been using VB6 to connect to an Access database using ADO with no
> problems. I then tried experimenting by trying to connect to Excel. The
> scenario is this:

> Once the connection is made I have a button that should count the number
of
> records. If connected to an Excel file then when this button is pressed an
> error is trapped which returns the message;

> [Microsoft][ODBC Excel Driver] Selected collating sequence not supported
by
> operating systyem

> If I then press the button again it returns the count correctly. Similarly
> if I first select an Access data source, do a count, and then change the
> data source to Excel, everything works fine first time.

> Can anybody point me in the right direction or suggest where I am going
> wrong?

> The function I use to build the connection string is given below. The
Excel
> bit looks terribly ugly - I just got it from looking at what UDL comes up
> with when connecting to Excel. A neater connection string for Excel would
be
> appreciated

> --------------------------------------------------------------------------
--
> -------------------
> Public Function myConnectionString(xdatabasename As String) As String

>         Dim strTemp1 As String

>         If LCase(Right(xdatabasename, 4)) = ".mdb" Then
>             myConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
>             myConnectionString = myConnectionString & "; Data Source=" &
> xdatabasename

>         ElseIf LCase(Right(xdatabasename, 4)) = ".xls" Then

>             myConnectionString = "Provider=MSDASQL.1"
>             myConnectionString = myConnectionString & ";Persist Security
> Info=False"
>             strTemp1 = "DSN=Excel Files;DBQ=" & xdatabasename &
> ";DefaultDir=c:;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
>             strTemp1 = """" & strTemp1 & """"
>             myConnectionString = myConnectionString & ";Extended
> Properties=" & strTemp1

>         ElseIf LCase(Right(xdatabasename, 4)) = ".udl" Then
>             myConnectionString = "File Name=" & xdatabasename

>         Else
>             xdatabasename = ""
>         End If

>        ' Debug.Print myConnectionString

> End Function
> --------------------------------------------------------------------------
--
> ----------------------------------------



Tue, 12 Aug 2003 16:25:40 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Runtime error '3228': Selected collating sequence not supported by the operating system

2. Collating Sequence not supported by OS

3. Selected collating sequence not supported by the operating system.

4. Err: [Microsoft][ODBC Text Driver] Selected collating sequence not supported by the operating system.

5. To collate or not to collate...

6. Problem with collating sequence

7. Collating sequence

8. Solution to Incorrect Collating Sequence problem importing Paradox into Access etc via ODBC

9. Selected Collating Sequence... Error 3228

10. Incorrect collating sequence in Paradox

11. Selected collating sequence error

12. Collating sequence?

 

 
Powered by phpBB® Forum Software