Disabling "Unicode Compression" property by SQL Query

Is it possible to have a SQL statement that can disable the unicode compression property on a column ?

Something in the lines of :


Sponsored Links:

Related Topics

Create Property
Using CreateQueryDef() to create Queries. Using the following code.

With DB
Set qdfNew = .CreateQueryDef("NewQryDef", "SELECT * FROM Categories")

End With

Works Fine.

I am having a problem in creating a Property using the CreateProperty Method to create the property "Description" so I can add remarks in the "Description" property as to what the query is intended to do.

Is there anyone out there that can share information as to how to use the CreateProperty to add the Description property and then fill in a remark?

Shortcut to SQL Query (View)?
Is there a keyboard shortcut to open a new query in SQL view?

I find myself encouraging carpal tunnel by:
> going to "queries"
> "new query"
> "design view"
> closing the "show table" dialog box
> right clicking the new query
> finally selecting "SQL view".

There has GOT to be an easier way that this 6-step process to ~run a query~ in a _database_ . but I haven't run across it yet though

inserting unicode control characters in CSV file
My Access project includes producing a comma-delimited (CSV) file for later presentation to an email program (LISTSERV Maestro). I'd like to be able to incorporate unicode control characters (e.g., CR/LF, etc) into the file to format the email. How do I do this? Here's the line that produces a record:

Print #1, EmailAddress & "," & sLastName & "," & sFirstName & "," & aCoursePrint(0) & "," & aCoursePrint(1) & "," & aCoursePrint(2) & "," & aCoursePrint(3) & "," & aCoursePrint(4) & "," & aCoursePrint(5)

For example, how could I insert a CR/LF after sFirstName and a Horizontal Tab (HT) before aCoursePrint(0)?

Changing the connection information on a linked QUERY in Access 2007
This is about linked queries, not linked tables. With no linked tables, the Linked Table Manager is disabled.

I know how to see the connection of a linked query, linked to SQL Server: ?CurrentDB.QueryDefs("create").Connect
SERVER=PRODSERVER;DATABASE=proddatabase;Trusted_Co nnection=Yes"
I need to change the server name in the connection.

I tried in the Immediate window
CurrentDB.QueryDefs("create").Connect = ".test server name."
but that did not change the connect property.

How do I change the connect property?

Avoiding duplication in a table.
I have an append query "QryInvoiceGrouped" and I have set its "top value" property to 1.

Therefore when I run this query it will append this 'top value' into the destination table "LedgerTxns".

but I need some setting in the table design grid to ensure this procedure cannot be doubled up by inadvertently running the append query twice.

A relevant unique field in the appended data is "OrderNo" and I need some function to insert into the "OrderNo" field property "ValidationRule"

Update query in access VBA
I m using access 2007. I want to use following update query in access vba but its giving error. Query is:

Docmd.RunSQL ("UPDATE property set property.status="& "'empty'" & " Where property.propertyid="& me.cmbpropertyid.value &"")

It gives the following error:
Missing ),], or item in query expression '(property.propertyid=4'.

Caption Property & Queries
How can I change the name of a Field in a query when the Caption Property has been set for the field in the underlying table? For example, table tblTest has a field named "Company Number". The Caption Property for this field has been set to "CompanyNbr". I want to create a query on this table and change the name of the field to "Co". I've tried "Co: [Company Number]", but the field name in the query is "CompanyNbr". I've tried "Co: [CompanyNbr]", but that results in a "Enter Parameter Value" dialog box for "CompanyNbr".

I realize I could simply delete the Caption. However, this is a database I receive weekly from an external source, and it has many fields in it.

This problem occurs in both Access 2003 &amp

Update Query - Record is too large
I am trying to update (2) fields from a temp table to a master table. Both fields are defined as memo, with unicode compression as No. I tried it as Yes as well but get the same error 'record is too large'. Do you have any suggestions to try?

I checked the field lengths on characters for the (2) fields and there is nothing over 255.

SQL Server Compatiblity "disabled" LIKE Operator
The database I am working on will eventually be connected to SQL server. As an experiment I checked the box, "SQL Server Compatible Syntax (ANSI92)" under "Access Options", "Object Designers", "Query Design". That "disabled" the following line of code:

strCriteria01PVT = "Ucase(Trim(ProjectName)) like ""*" & UCase(Trim(Me.Text4)) & "*"""

Of course, unchecking the box restored the operation of the code.

Is there any benefit to selecting the option: "SQL Server Compatible Syntax (ANSI92)"?

Column Headings For Crosstab Query
I am passing a sql statement for a crosstab query, as such, in VB Code:
"PIVOT myField IN ('Apple', 'Orange', 'Banana')

CurrentDb.QueryDefs("myQuery").SQL = strSQL

If I look at the ColumnHeading property in design, it's set to 'Apple', 'Orange', 'Banana'

The query in Access however is sorting as such. Banana, Orange, Total, Apple

Why isn't my PIVOT IN line sorting the column headings in the order I want!?!?

Saving Query result to Table
My code creates and opens a query. The original query was an append query and I need the same from this query or way to save into table "tblMODfnr".

All HOWTOs and helps I've read, do not show a way to do this and when I click "File" + "SaveAs" with the query open, table is not an option.

How do I get this done?

Do I need to change my SQL to "INSERT INTO" and then "DoCMD.RunSQL

Query gives "Property value is too large" while saving?
This query generates a report out of several united queries and tables. I know, it looks like spaghetti, When I try to save it, it tells me: "Property value is too large".

It does not stop the query from working and I do get the results I want. Just; where might this error come from?

attached tables and queries are linked through "Ordered Item" textfield, with unique value (in the 107 baseline query). The result would give something like 40 columns max (depending upon input on the '107' baseline).

Calculated Query - property sheet not displaying option for Currency
I am trying to create a calculated query using an "IIF" statement in the "FIELD":

Natl Assess: (IIf([Let Date]>#12/31/2010#,([Bid Price]*0.55)/1000,"0"))

This returns the correct value, but I want it to display as currency. My property sheet for this field does not have a "currency" option.

Should I put the "Iif" statement in "criteria"?

How to save prtDevMode to table?
I'd like to take current form's prtDevMode property and save it to the table field, as is, without any changes, Memo field can of course take any size of a data, but when I try to insert/save it I receive "The width of a Unicode text column must be an even number of bytes." error.

How to save it to a table field and then later retrieve it to the same form as when read from form's .prtDevMode property? Is there some form of conversion necessary prior to saving to tablefield and then opposite when returning to original form?

"Display on SharePoint Site" property of a form or report is missing from my property sheet
It should appear just above the "modal" property.but its not there. How do I enable this?

Running two SQL Statements
I have one query, where I added the leading "0's" and the SQL statement I used to do this was:

SELECT =Format([SKP B],"00000") AS Expr1
FROM Sheet1;

Then from this query, I want to combine the SKP B (with the leading zero's) and SKP A into one row in Access, and use the following SQL statement to do that:

SELECT [Field1] & "-" & [Field2] AS Field1_Field2
FROM Sheet1;

The problem that I run into is that when I run the 2nd SQL statement the leading "O's" are dropped.

I know that you can do this in two separate queries but I would much rather do it in one if that is possible.
So my question is, is there a way to run two SQL statements in one query?
OR is there a way for me to combine both of these statements into one SQL query?

error on inserting value
anyone can tell me what's wrong with this code? it keeps on giving me error "Number of query values and destination fields are not the same". please help.

btw, cTime and ccDate are Textbox

Dim sql, in_sql, out_sql As String
sql = "INSERT INTO Transaction(TimeIn , EntryDate) VALUES ('"
sql = sql & cTime & "," & ccDate & "')"
DoCmd.RunSQL (sql

error on inserting value to the table
anyone can tell me what's wrong with this code? it keeps on giving me error "Number of query values and destination fields are not the same". please help.

btw, cTime and ccDate are Textbox

Dim sql, in_sql, out_sql As String
sql = "INSERT INTO Transaction(TimeIn , EntryDate) VALUES ('"
sql = sql & cTime & "," & ccDate & "')"
DoCmd.RunSQL (sql)

Pivot with "Iif" statement in SQL
I have a query that worked using the "Switch" option in the Pivot Statement.
I needed to ensure that if in some instances there was no data available in a given Selection of records for one of the fields the Pivot would create, that the query not crash but return a null or "0" value in that field.

To accomplish that, I have added an "Iif" statement to the query.


I get an error now that their is a data mismatch in the query criteria.


TRANSFORM Count(tmp_PA_Att_Range.Type) AS CountOfType
SELECT tmp_PA_Att_Range.WCode, tmp_PA_Att_Range.Type, Count(tmp_PA_Att_Range.Type) AS [Total Of Type]
FROM tmp_PA_Att_Range
GROUP BY tmp_PA_Att_Range.WCode, tmp_PA_Att_Range.Type
PIVOT Switch(IIf([Credits]=1,"1 Credit","0"),IIf([Credits]=2,"2 Credits","0"),IIf([Credits]=3,"3 Credits","0"),IIf([Credits]>=4,"4 or more Credits","0"));

Access 2010, Windows 7.

Skill Level - SQL beginner

What is wrong with this sql?
Dim SQL as String

SQL = "SELECT [Sales Orders].InvoiceDate FROM [Sales Orders] WHERE ((([Sales Orders].InvoiceDate)>=#12/16/2009# And ([Sales Orders].InvoiceDate)<=#12/31/2009#))"


Why does the above SQL give me a 2342 error, "A RunSQL action requires an argument consisting of an SQL statement?" I copied it direct from a query using the query design tool.