Solved Unicode compression when creating a table

[zelarra821]

Hello.

I've been thinking about it for a while but I can't find the error.

I have this code to create a table, where I want to put the unicode compression in the "Name" field, but I get a syntax error.

I'm sure it's a silly thing, but I can't find any information on the Internet and the information provided by Microsoft doesn't solve anything for me.

dbs.Execute "CREATE TABLE TEvents " _
& "(EventDate DATETIME, Name CHAR WITH COMP)"

Thanks!!!

 

[Josef P.]

Try ADODB.Connection.Execute

CurrentProject.Connection.Execute "CREATE TABLE TEvents " _
                                &  "(EventDate DATETIME, Name CHAR WITH COMP)"

or

CurrentProject.Connection.Execute "CREATE TABLE TEvents " _
                                &  "(EventDate DATETIME, Name nvarchar(255) WITH COMP)"

 

[zelarra821]

        Dim dbs As Database
    
        Set dbs = Application.CurrentProject
          
        dbs.Connection.Execute "CREATE TABLE T00CopiasDeSeguridad " _
            & "(Fecha DATETIME, Nombre CHAR WITH COMP)"

        dbs.Close
        
        Application.RefreshDatabaseWindow

I have tried both, but I am copying here only one of the tests, and it tells me that the types do not match.

 

[sonic8]

Is "WITH COMP" a valid abbreviation of "WITH COMPRESSION"? - I don't think so.

Application.CurrentProject is not of type Database! The type is also named CurrentProject.

Dim prj as CurrentProject
Set prj = Application.CurrentProject
prj.Connection.Execute "CREATE ....

 

[Josef P.]

dim cnn as adodb.Connection
set cnn = CurrentProject.Connection
cnn.execute ...

Is "WITH COMP" a valid abbreviation of "WITH COMPRESSION"?

It works with adodb.

 

[zelarra821]

I've already managed to get it working.



Now, this doesn't fix the problem for me.

        Dim prj As CurrentProject
    
        Set prj = Application.CurrentProject
          
        prj.Connection.Execute "CREATE TABLE TEvents " _
            & "(DateEvent DATETIME, Name CHAR WITH COMP)"
        
        Application.RefreshDatabaseWindow

I'll tell you:

1. I create the table (using the code I posted above).

2. Then, I enter values using VBA like this:

Dim rstEvent As DAO.Recordset

Set rstEvent = CurrentDb.OpenRecordset("TEvents")

rstEvent.AddNew
rstEvent!DateEvent= Now
rstEvent!Name= "Test"
rstEvent.Update

However, it adds a lot of spaces at the end of the Name field.

This morning, while testing, I thought I had found the solution: check unicode compression in the Name field.

However, I was disappointed that this was not the problem.

 

[sonic8]

However, it adds a lot of spaces at the end of the Name field.

That is expected behavior. The CHAR data type is padded with spaces to its full length.
You are probably looking for the VARCHAR data type.

 

[zelarra821]

You are probably looking for the VARCHAR data type.

Awesome. Thanks a lot!!!!

 

[arnelgp]

you may also try:
Currentdb.Execute "CREATE TABLE TEvents (EventDate DATETIME);"
currentproject.Connection.Execute "Alter Table TEvents Add Column [Name] Text(255) With Compression;"

 

[zelarra821]

ok thanks