Correct SQL Query, DNS Less conn.

[Geirr]

Hi

I've run into a case I hope I can get some directions / help on.

Background: Moved a Back End to SQL Server. The server management gave me a user name and pw for access to the sql datafile, used when setting up a DNS to the server, using SQL Server driver.

Now, for some reason I've been asked to change from linked tables with DNS, to direct connect to each table with rsTable.Connection = "Provider=SQLOLEDB;SERVER=SQLnn;DATABASE=nnn_cds;UID=SQL_nnn_cds;PWD=xxxxxxx;"
That's works fine, no probs.

Now I'm converting all queries to selecting Pass Through, and mostly with a clean copy of the sql-string works fine, but when I reffering the (WHERE) to a forms field value, I get the error message; ODBC--call failed .... incorrect sytax near '!'. (#102)
To get standard SQL string to run, I found that the following ODBC Connection string in Querry design works fine:
ODBC;DRIVER={SQL Server};SERVER=SQLnn;DATABASE=nnn_cds;UID=SQL_nnn_cds;PWD=xxxxxx;

I believe usage of - ! - in sql string makes the problem. The actual sql string for one of the queries are:
SELECT Table.Field1, Table.Field2, Table.Field3
FROM Table
WHERE (((Table.Field1)=[Forms]![Ad_VarForm]![txtProjectNo]))
ORDER BY Table.Field2;

Other queries without the usage of ! have worked perfectly when using just a copy from 'old' sql-view in query designg, to the new sql-view after selecting the Pass-Trough.

Please excuse the 'long' message, but I don't now better how to explain this problem... and excuse my lousy english writng...


Brg, Geirr.

 

[theDBguy]

Hi Geirr. If you’re using a pass through query, then I think the problem would be the parameter referring to a form in Access. A pass through query is processed by SQL Server, and it won’t know or have access to the form in Access. You will have to modify the SQL statement to resolve the parameter before running the query.

 

[isladogs]

Agree with DBG. You can't run a passthrough query involving an Access based reference such as a form control.
If you still have linked tables using the DSN less connections, I would just use Access queries or SQL statements rather than trying to resolve the parameters.

 

[Geirr]

Thanks for the replies!

I understand what you are saying, but I just don't know how to figure out how to solve this issue. If you look at the querry, this is a record source to a combo box. So what I did, was just go into the RSource, and changed the underlaying query (based on inked tables), to select Pass Through. I then added the Connection string in the ODBC Connect Str. field in Prperties list. This worked just fine on queries without adressing a form field vallue (which I really need to have several places..).

So, if I ask really nice - based on the query above - how should I solve this in the proper way?

Due to the usage of my prog., I would prefere (forced to..) to hardcode the access to the server in connection strings, avoiding using a DNS conn. - and not use linked tables. Will end up in a .mde front end.

Brg. Geirr.

 

[isladogs]

Have a look at this example Encrypted Split No Strings Database

Ignore the encryption part. Just focus on how I have set the record source for the forms and report using connection strings in the code. There are no permanent table links

 

[Geirr]

Hi isadogs.


Thanks for your efford, but this issues are little new for me, and I'm really strugling on how I can 'transform' the value I need in the Sql for record source.


Brgh. Geirr,

 

[isladogs]

I hope you don't mind me saying but this is not a beginners' project.

Suggest you start by using linked tables.
Then create your forms/reports based on those linked tables.
Convert the record sources to SQL statements and copy to the form load event

e.g. Me.RecordSource = "SELECT ... FROM ... WHERE ... ORDER BY ..."

Now remove the record source from the form's property sheet.
Save / close / reopen the form. Does the form still work properly?
Repeat for each of your forms/reports.

When you have done all objects in the same way, remove your linked tables & test the database still works properly

Good luck

 

[Geirr]

As I wrote earlier, it's based on a existing solution using linked tables to an sql server. It's a prject I have built up over a time. I'm familar with setting up queries based on query designer, based on linked tables, releated or not.

Based on some issues related to the company I'm working in, I where suggested to not use a DNS, but rather with directly linking as statded above. No problem with this so far, but I ran into trouble when I tried to use sql-strings in queries based on values/filter from form-fields. So the whole case is about finding a workaround for this matter. Whats really new for me, is to use an alternative soultuion without a DNS connected linked table.

I'm absoulty not affended about your post, but maybe some other see the case and maybe can explain me how t 'translate' the querry staded in my initial post, into a working solution.

Brg. Geirr.

 

[theDBguy]

...but maybe some other see the case and maybe can explain me how t 'translate' the querry staded in my initial post, into a working solution.

Brg. Geirr.

Hi Geirr. Pardon me for jumping in... Let me give it a shot.


So, as previously explained a SQL statement like SELECT * FROM TableName would be fine as a passthrough query because SQL Server has access to everything it need to resolve that request. But it will have an issue with this one: SELECT * FROM TableName WHERE ID=Forms!FormName.ControlName because it cannot resolve any reference to an Access object. So, the goal is to convert the above SQL statement where the form reference is already resolved before asking SQL Server to evaluate the SQL request. For instance, let's say the form's ID value is currently a 1, then we'll need to change the above SQL statement to something like SELECT * FROM TableName WHERE ID=1, and then SQL Server will not have any problems running it.


So, I guess the question is how to make that conversion? One way is to use a QueryDef object. So, again, let's say you have a passthrough query called Query1 with the above SQL statement. We could modify it using VBA as follows:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("Query1")
qdf.SQL = "SELECT * FROM TableName WHERE ID=" & Forms!FormName.ControlName
Set qdf = Nothing
Set db = Nothing

If you view the query's SQL statement, you'll see it was changed to a resolved ID value. When you run this query in SQL Server, it should work now.


I guess the additional problem with your situation is because you're using this query in a combobox. This means each time the user tries to use the combobox, you may have to modify the query, so it will give you the correct results. You could try running the above code in the Focus event of the combo just to see if it will work.


Good luck!

 

[Geirr]

Hi theDBguy and isladogs.


First, thank you for your time.


After a new walkthroug with the IT guys in our company, I've got acceptance to go back to using DSN and Linked Tables. This mostly when they understood my side of the case, dealing with around 20 different forms, and ca 35 queries used as recordsource for forms and several lists...
It seems like the whole case started with some misunderstanings around the ODBC driver versions... It's the case where a smal detail where windig up to a huge case...


Anyway, keep up your good job helping people


Brg, Geirr.

 

[theDBguy]

Hi. Glad to hear it’s no longer an issue. Good luck with your project.

 

[NauticalGent]

So...I came across this same issue at work today. I have become obsessed with doing as much as possible with SQL Server - even to the extent of connecting subforms directly to SS (a technique from Steve Bishop)

I wanted to base ALL my forms on a direct connection to SS, but it was proving difficult. PTQ's are not updateable, Table Views are good but I could not figure out to to dynamically alter them to pull one record. The same with Table Valued Functions, they accept parameters, but as with PTQ's, they are not updateable.

While I was looking at the Property Sheet of a select query, I noticed two properties: Source Table and Source Connect Str:

On a whim, I pasted the connection string from one of my PTQ's and it worked! Why have I not heard of this before? After trying to figure out a way to pass a parameter to it (and failing),

I held my nose and resorted to one of my old hacks and used a Form reference in the criteria ([Forms].[frmMyForm]![PKField], placed the connection string in the Property valued and it works like a charm. I didn't notice much of a performance boost, hopefully the remote sites will.

I would like to think I stumbled across a hidden jewel but I am sure that this technique is known and that if had any merit, it would be talked about more.

Thoughts?

 

[Galaxiom]

SourceConnectStr and SourceDatabase are generally used to connect to backed end databases when linked tables are not supported. Ultimately the query is still translated by the ODBC connector so the performance will be much the same as using linked tables.

It is often assumed that Access queries connected to SQL Server are processed locally by Access. However, provided the query is not too complex, the ODBC layer will actually pass the query to the server for processing.

 

[NauticalGent]

Thanks G, the performance factor/info is what I was looking for.

 

[Galaxiom]

I can see you have been exploring a variety of ways to use the server with Access. There is at least one more.

Return an ADODB Recordset from the server using an ADODB command directed to a Parameterised Stored Procedure.
Use these property settings.
CursorType: adDynamic
CursorLocation: adUseClient
LockType: adLockBatchOptimistic

Disconnect the Recordset by changing the ActiveConnection to Nothing.

Set the bound Form's Recordset property as this recordset.
Set Me.Recordset = rsWhatever

This recordset is now held in your Access form and is updateable on the form.
Even recordsets based on funky joins that would never be updateable become updateable on the form.
It is like having a temp table without having a temp table.

BTW I use this technique to add a Boolean (bit) field to the recordset to support a selection checkbox on the form. The bit column comes from a single record table on the server with a Cartesian join to the main data to be displayed. The disconnection makes the field independent for each record on the form.

Write the changes back to the server by looping through the Form's Recordset and generating commands to execute another parameterised Stored Procedure which you can send by ADODB Command.

If it is an intrinsically updateable query you might instead try reconnecting the recordset then running the UpdateBatch Method of the recordset. Not sure if this will work though after being set as the form's recordset.

I use disconnected recordsets for displaying data but don't have much call for updating the original data so I have not fully explored the possibilities.

 

[NauticalGent]

Perfect, a new technique to explore! Thanks so much