Connecting to MS ACCESS and trying to execute Query

Applications Control
#1

Hi all,
I did the connection to Ms Access Database and now I have to make some queries.
There is a problem becouse some fields in access are enclosed by square brackets [field_name] .
If I want to run for istance a query like this:

$sql_customer_table=“SELECT Left([CodAnag],1) AS CLI_FOR, Anagrafica.CodAnag, Anagrafica.[Ragione sociale], Anagrafica.NomeContatto, Anagrafica.Indirizzo, Anagrafica.Localit?, Anagrafica.Provincia, Anagrafica.CAP, Anagrafica.Nazione, Anagrafica.NumeroTelefono, Anagrafica.NumeroFax, Anagrafica.Email, Anagrafica.Codag
FROM Anagrafica
WHERE (((Left([CodAnag],1))=“C”));”;

Obviously … SC intercept [xxxxxxxx] as global variables … ;-(

Anybody has done such kind of experience ??
Many Thanks

#2

One of the mystics “features” from SC …

#3

Ehi… Now I’m facing a bigger problem…
I used the ready to use MsAccess connection , after having create a new ODBC Client on Window… and testing the connection that give me OK .
Saved…
Now I go to see tables using SQL Builder and… WOW Oh my God !!! Any table available … actually completly empty.
What that it means ???
Is it a my mystake ???

#4

OK, I’ve used now standard ODBC connection (no MsAccess preconfigured) and magically tables appear… ;-).

What can I do with SQL ??? to avoid the [ ] problem ?? It sounds really strange that nobody has had yet this problem.

#5

I check it now and I understood why …The square bracket are added when the name of msaccess field field has blank spaces inside the name or conteins a special chars.
I’m not a techy guy and I don’t know how can solve it or overtake it with a workaround.
If I use … “AS” after the name of field could be a solution ?? I think that SC detect in any way the presence of something between square brackets and interpret it as global var… ;-(

#6

The only solution I can think of is to create a query in msaccess and call that query.
So create a query SELECT Left([CodAnag],1) AS CLI_FOR, Anagrafica.CodAnag, Anagrafica.[Ragione sociale] as ANRAG, Anagrafica.NomeContatto, Anagrafica.Indirizzo, Anagrafica.Localit?, Anagrafica.Provincia, Anagrafica.CAP, Anagrafica.Nazione, Anagrafica.NumeroTelefono, Anagrafica.NumeroFax, Anagrafica.Email, Anagrafica.Codag
FROM Anagrafica
WHERE (((Left([CodAnag],1))=“C”))

Name that query for example qry_Anagrafica and do a select CLI_FOR, Anagrafica.CodAnag, ANRAG, Anagrafica.NomeContatto, Anagrafica.Indirizzo, Anagrafica.Localit?, Anagrafica.Provincia, Anagrafica.CAP, Anagrafica.Nazione, Anagrafica.NumeroTelefono, Anagrafica.NumeroFax, Anagrafica.Email, Anagrafica.Codag FROM Anagrafica

I assume here that a query is also visible from odbc. I havent tested this tho.

#7

Hi RR,
that’s correct !!! That’s exactly what I casually did Yesterday (set myself on… desperation…mode) and it works !!!