Form_Load

[JezLisle]

I thought that using ADO there was no need to link tables.

Even if we didn't want to link tables, you still can write queries referencing tables not linked by filling in the ODBC connect string property of the query in the query builder. The string should be same one as you used in code (formatted as single unbroken string without quotes of course).

how do you mean by this, how can I do this?

 

[Banana]

Well, you certainly can use ADO or even Jet's query or DAO to reference a table that's not linked. However, on average, the operation will be more expensive because all work done in locating and interrogating the source for the information in accessing that table has to be done each time that table is called upon whereas linked table cache that information and minimize the forth-and-back between the client and the server at expense of a slightly longer start-up. In most cases, I'd rather my users wait a few seconds more at startup than between forms or navigations.

Furthermore, you still can use ADO with linked tables as well.

I'm not quite clear why you feel that those table shouldn't be linked. Normally, I'd just link tables and go unlinked only if there were performance issues.


In query builder, open the properties windows (Alt+Enter). You may need to make sure you're looking at Query Properties, not Field Properties (to do so, click on the gray area where you place the tables in query builder). The property is "ODBC Connect Str" and it should be something like "ODBC;sqloledb;...", same as one you used for your ADO connection.

 

[JezLisle]

I have tried that and still same message.

Could not find file 'F:\My Documents\jez.mdb - I dont know what jez.mdb is as there is no database with that.

I've inclued a screen shot of what I done to make sure its correct

 

[Banana]

It's probably because Access thinks you want another Access database; You need to prefix the whole connection string with "ODBC;" so it knows that it is a ODBC connection and look for a driver instead (e.g. sqloledb) instead of an Access database named jez.mdb (which of course is nonexistent).

 

[JezLisle]

It's probably because Access thinks you want another Access database; You need to prefix the whole connection string with "ODBC;" so it knows that it is a ODBC connection and look for a driver instead (e.g. sqloledb) instead of an Access database named jez.mdb (which of course is nonexistent).

I have tried 2 different ways of writing the connection strings as shown.
ODBC;sqloledb;Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI
ODBC;Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI;
Still I get the error message - Could not find file 'F:\My Documents\jez.mdb
Really dont understand, I have enen tried it in a new database to see if that changed think and still same error, I thought that as was under a schema of my own in SQL that may affect but tried in the normal dbo schema and same error appeared apart from changed from jez.mdb to dbo.mdb

SELECT PatientName, DateOfBirth, PatientRef
FROM [dbo].IC_ReferralRecord IN MyDatabase["ODBC;Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI;"]
WHERE ((([InputBy])<>'DONOTDELETE'))
ORDER BY DateOfBirth;

 

[JezLisle]

Is a Pass-Through query same as this?

 

[Banana]

Sorry for not taking time to respond; I had fires to put out.

First, I am pretty sure there shouldn't be a 'MyDatabase' outside of the IN[..]; it should be something like FROM [dbo].IC_ReferralRecord IN "" [ODBC;...], but I'll have to dig up my old query to verify the correct syntax.

You could certainly try a pass-through query.

I may have had missed it; but why not just link it?

 

[JezLisle]

No worries, I have tried the query as below and still no joy.

SELECT PatientName, DateOfBirth, PatientRef
FROM [dbo].IC_ReferralRecord IN ''["ODBC;Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI;"]
WHERE ((([InputBy])<>'DONOTDELETE'))
ORDER BY DateOfBirth;

I have tried the Pass-Through way and that works. The only problem I have is that a pop up box appears asking to select a data source, or if I choose that and click save password it allways uses my login which is no good for anyone else, I need a generic command.

 

[JezLisle]

I am still having no joy with this below, it keeps telling me that it cant find the dbo.mdb, I dont understand why its even looking for an mdb file when I have input a SQL server Connection string. How can this be? I thought ADO was designed for the abilitity to connect with/without Linked Tables. Is the only way then to use Linked Tables?

 

[Banana]

It's not the only way, and I thought of one more possibility: What happens if you omit the [dbo]. from the table name and just use the table name itself?

Failing that, try Carl Prothman's site for some hint on connection string. Of course, it must be always prefixed with ODBC. I'm wondering if the sqloledb isn't understood and thus requires a ODBC driver. Here's a starting point.

I'm still not sure why linked tables is being avoided here? It's usually much easier simply linking and as I said before, you can use ADO against a linked table as well as non-linked table, and in fact, you'd get better performance using linked table than connecting to a ODBC source on fly via a query because Access will cache all necessary information at linking time, saving you time and effort from looking up the parameters each time you run the query.

 

[JezLisle]

Thanks Banana, I've had a look at the website and this connection string

"[COLOR=#0000ff]ODBC;Driver={SQL Native Client[/COLOR]};" & _
           "Server=MyServerName;" & _
           "Database=myDatabaseName;" & _
           "[COLOR=#0000ff]Trusted_Connection=yes[/COLOR]"

has worked at home, the test is at work, still should be ok. If it doesnt then I will revert to Link Tables.

Thanks for the help so far