CODEWITHSUNDEEP
sqlsql-servervbams-accesssql-server-migration-assi
Lewistrick
Lewistrick

Reputation: 2879

How to prevent Access from removing square brackets around a column name in a querydef?

I want to convert an Access database to SQL Server. For this, I use SQL Server Migration Assistent.

One of the problems I encounter is that one of my columns is called Current and it's accessed in a query. I wrote a script that edits all querydefs and puts all column names in square brackets so that the edited version would be accepted by SQL Server.

I use this line in VBA to edit the SQL in the querydef:

CurrentDb.QueryDefs(queryName).SQL = newQuery

But it removes the square brackets. This causes SSMA to raise an error about the query, which results in the query not being migrated to a SQL Server view.

Is there any way I can prevent Access from removing the brackets when editing the SQL of the querydef?

The editing process works fine, for example when I give it this querydef:

SELECT [TableName].Current FROM [TableName];

it returns this:

SELECT [TableName].[Current] FROM [TableName];

But when I edit the query and open it in Design View, it removes all square brackets:

SELECT TableName.Current FROM TableName;

Also, when I manually edit the query, save&close, and reopen it, the square brackets are not removed (and I'm sure it was saved because other changes to the query are changed correctly). But manual editing is not something I want to do, because there are over 200 queries I need to edit.

Upvotes: 1

Views: 82

Answers (0)

Related Questions