Try to Get Around with #-Sign in Field Name in Paradox Data

[jaykchan]

I am trying to load data from a Paradox table into a database server
using MS Access. The problem is that some fields in the Paradox table
has a '#' in the field name, like "Freight#". I cannot query on those
fields using this kind of statements:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "select Freight# from JobInfo", _
cnn, adOpenForwardOnly

The rst.Open statement doesn't like the reference to "Freight#", and it
gives this error message:

[Microsoft][ODBC Paradox Driver]
Syntax error in date in query expression 'Freight#'.

How can I get around with this problem?

MS Access has no problem opening the Paradox table that has that
straight field name in interactive mode. This means there must be a way
that MS Access can handle this.

Any idea? Thanks.

Jay Chan

 

[Dirk Goldgar]

I am trying to load data from a Paradox table into a database server
using MS Access. The problem is that some fields in the Paradox table
has a '#' in the field name, like "Freight#". I cannot query on those
fields using this kind of statements:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "select Freight# from JobInfo", _
cnn, adOpenForwardOnly

The rst.Open statement doesn't like the reference to "Freight#", and
it gives this error message:

[Microsoft][ODBC Paradox Driver]
Syntax error in date in query expression 'Freight#'.

How can I get around with this problem?

MS Access has no problem opening the Paradox table that has that
straight field name in interactive mode. This means there must be a
way that MS Access can handle this.

Any idea? Thanks.

Jay Chan

Put square brackets ([]) around any field or table name that contains
nonstandard characters. For example,

rst.Open "select [Freight#] from JobInfo"

 

[jaykchan]

Put square brackets ([]) around any field or table name

that contains nonstandard characters. ...

Great! This works. I didn't know that this is that easy.

Thanks.

Jay Chan