Different DataSource then linked table?

[Krava]

Hi, folks!

I am fighting with one idea with no success.
I have a data in MySQL (MS SQL) and the access client, normally with linked tables in it.
But is it possible that I have a chance to avoid linked tables at all and to set up (somehow) the datasource/recorsource for forms and its objects (Comboboxes) via e.g. ADO? I was trying to figurit out but no success. The idea behind is to have a stored procedures on the SQL server that I would call with ADO and the result would be as data source for Access objects.

Thank you in advance.

Krava

 

[Banana]

You don't have to 'avoid' linked tables. If you need to call a stored procedures (and are okay with it being read-only or not expecting a result set in return), you can just use pass-through queries which allows you to write SQL query in the backend's native SQL and reference the server-side objects as you like.

Alternatively, you can set a ADO recordset to the combobox, rather than binding it the usual manner. If you do choose to do so, you have to explicitly specify that ADO recordset's cursor is located client-side, and is Static type. Otherwise, it will not work correctly.

Hope that helps...

 

[Krava]

Dear Banana!
Thank you for your quick reply.
I am not quite sure with the first part of your reply.
Concerning the second part- I have done following:
1) I have placed ADODC ActiveX DataControl object on one Form.
I set up Connection string (working) and its RecordSource as "StoredProcedure" and then the ProcedureName.
I have placed ListBox object on the form and also one Command button. By clicking on command button I wanted to try to set ListBox.RowSource to ADODC via VBA code, but I could not figure it out. I know how to do in VB6.0.
Any clues? Hints? I am sure I do something wrong.

Many thanks in advance.

Krava

 

[rapsr59]

If the title of your Thread "Re: Different DataSource then linked table?" implies you are trying to set the Record Source for a form, or the Row Source for a combo box, without having to use linked tables...

"avoid linked tables at all and to set up (somehow) the datasource/recorsource for forms and its objects (Comboboxes) "

You can set up the Record Source / Row Source, without the use of linked tables, by using sql as the source for each. The following example is the Row Source for a combo box in a Front End database that returns the categories from a Back End database.


SELECT tblCategories.* FROM [C:\Users\Richard\Documents\BackEnd.mdb].tblCategories;


The following example is the Record Source for a form in the Front End database that returns the Product Records from the Back End database without the use of a linked table.


SELECT tblProducts.* FROM [C:\Users\Richard\Documents\BackEnd.mdb].tblCategories;


You can add, edit, or delete these records the same as if they were linked tables in the Front End db.


I hope this helps.



Richard

PS: You can do this to all of your linked tables and your users will not have access to the tables since there are no linked tables!

Banana! Here is the answer to "Bill bought a fancy new porsche. It is capable of accelerating at 9.81 m/s² without using any gas at all. How is that possible?" It was a battery operated R/C model.

 

[Banana]

Two questions...

1) Are you using an ADP?

2) Why do you feel you need ADODC ActiveX DataControl object? I've never used it before, but am wondering why you would think an ActiveX control would do a better job of meeting your specs than Access's builtin control(s)?

As for my first part- If you create a new blank query in query builder, then right-click on the gray area and select SQL-Specific -> PassThrough, the query will move to SQL view and you can now type in native SQL. In case with MySQL, you could pass a query as if you were using MySQL's client;

SELECT COUNT(Population) FROM Country GROUP BY Continent WITH ROLLUP;

This would not work in other queries because Jet does not understand the 'WITH ROLLUP' clause, but in a PassThrough query, you can do just that and get the exactly result you would get in a MySQL client.

The other step you need to do is to fill in Connection String so the query will know where to locate the MySQL server. Of course, this only is available on MDB/ACCDB. If you're using ADP, this isn't even applicable at all and you're pretty much tied to SQL Server, AFAIK.

 

[Krava]

Dear Banana!

Thank you for your suggestions.
Well, we do not use ADP. Normally, we distribute MDE clients.
Why ADODC? It was only the idea as we have experience with it in VB6.0 developing. We developed a hige system for collecting the data from production (BarCodes) and we store it into MS SQL 2000 server. The client is EXE file developed in VB6.0 and ADO offered us much better performance and feature then DAO we used to use before. But in our new project we consider MS ACCESS front-end to a few reasonable reasons. Only question for us was to provide as much speed as possible in the view of data writing and data selections from MySQL Back-end. The backgroud- we have two physical locations, two internal LANs. They are inter-connected via VPN. The DB will be located in one of internal LAN only. So the remote clients will obtain the data via VPN and we have to provide the maximum of the speed as we can (response time). And, we have never done it before as this structure.
I am pretty sure there is a lot of field to be discovered.

Thank you and I will keep you updated with the result of our testing.

Kindest regards

Andrej

 

[Banana]

Krava,

Sorry about the ADP- I was getting confused and wanted to make sure. It wouldn't have had made sense if you were using MySQL.

As for ADODC, I've not used it before so don't know what it would be exactly...Perhaps if you described a bit about its functionality, we could suggest how this could be done effectively as possible using native controls?

Having had developed & implemented MySQL-Access system across three remote offices, here's my experiences. Of course, our network topology may be radically different so do take it with a grain of salt. We had VPNs, but the server handling VPN request also acts as file servers along other things and thus is already overburdened with processing the requests. For that reason, I chose to connect directly to the database server via WAN using IP. Of course that required extra work in providing extra security to ensure that the communication between client and server is secure. Using SSH to tunnel the connection, the performance was quite fast despite running MySQL as dedicated process on fairly old hardware.

In that implementation, I didn't use ADO, simply because I saw no reason since all of my bound forms' performance were satisfactory. I always used queries and never tables and enforced a WHERE clause even if only to restrict to a reasonable set of data that my users are much more likely to want to browse to. If they need to browse beyond the default, they can do so by taking an extra step.

Of course, this is not to say that DAO should be used exclusively, after all we can set ADO recordsets to form and it'll act just as any other bound forms, and ADO does have its niceties. It just happened that in this case only, I didn't find a use for ADO.

Jet is quite smart in making reasonable requests to any back-ends so 90% of the time, any slow requests can be blamed upon developers (that's me! ). For that reason, I usually encourage others to read the Jet/ODBC Connectivity whitepaper which can be retrieved from support.microsoft.com; it details how Jet will process various SQL requests and how poorly formed SQL could force local evaluation (not good).

I hope this helps some.



Richard - Nice try, but consider that 9.8 m/s^2 = 33 ft/s^2 and you get the idea of how fast it is...

 

[Krava]

Dear Richard, dear Banana!

I am back to you with some feedback.

At first- thank you very much for you ruseful support!
And- both suggested methods worked out well, I am very impressed. Thank you.
Banana- only one unclear point is- what WITH ROLLUP statement means? Any specific function? I have tried to implement it into my SQL statement and the error from MySQL server was returned. Without this bit it works fine.

However, ADO vs DAO- hard to answer from my side as our experience with ADO is good but we never went too deep in studying all details about ADO. The reason was no time "factor".
But what I can really prove is following. Before (a few years ago) we used to use for our clients (VB6.0) standard DAO. But the number of clients was increasing, the same was as number of connections, transactions and data volume on SQL server. We faced some problems with response time and then we found ADO. We applied all active transactions to ADO (connection string, recordsets, calling stored procedures) and we experienced a huge difference in possitive way. The whole system sped up rapidly.
These days we have the same experience- if I use DAO for e.g. deleting the table, it takes 40 seconds. If I use the same but using ADO, it takes one second. What to say. But maybe we still do something wrong.

Concerning the VPN and SSL, unfortunately this is given LAN structure and it will be difficult to change it, as the third location is connected as well- it is our mother company in Germany. Fortunately, they do not need to connect to our systems.

Anyhow- it is very positive that we learn a new things every day and I am very grateful that there is somebody out there who is willing to help- like you and Richard.

So thank you very much one more time and I hope I will be able to give you some clue as well.

I think I will keep you updated with further test if you like.

With kindest regards

Andrej.

 

[Banana]

Krava,

I'm not sure why it didn't work. One thing I noticed is that I gave a COUNT() when I was thinking of SUM() but couldn't confirm if it's only for SUM(). MySQL has an explanation of what WITH ROLLUP does and does better job explaining than I could. The point being made here is that when you use pass-through queries, you can write in MySQL's dialect, rather than Jet's dialect and take advantage of MySQL's extensions and optimizations.

Regarding ADO/DAO, it looks like you've fond what works for your case. All more the power.

As for VPN/SSL, to clarify, I wouldn't say that you have to change your VPN. All you would have to do (at least from what I'm seeing) is open a port on the gateway/router managing the LAN that has MySQL server on it and have it porward all communication from that port (can be any port number) to MySQL's listening port (usually 3306). Therefore your clients would connect to the external IP at that port number to access the MySQL outside of VPN without any changes to VPN made. Of course, your IT policy may not make this a option at all, but I was worried that I didn't make that clear and sounded as if VPN had to be modified when it doesn't have to be

I hope this helps some, and would be more than happy to help out with any questions that may arise in your project. Best of luck!

 

[Krava]

Hi, Banana!

Well, I think I have made a mistake.
In my test, I used SELECT statement and I think it is the problem.

Concerning your suggestion about SSL I think it makes sense. Since I am not a specialist in this field I passed your suggestion to my colleauge and I am sure we will study it more.
And, I will keep you updated.

In general- you helped a lot.
Thank you very much for that, again.

Sincerely,

Andrej.

 

[Krava]

Hello, Richard!

I am back to you!
All works fine, but I have one problem.
The syntax you suggested works fine with SELECT of one table.
If I created SELECT statement with INNER JOIN of two tables, I got the error message that the system cannot find the second table.
Any clue or hint how to fix that?
Because I work with MySQL Backend I used ODBC string in the brackets instead the path to *mdb data file.

Thank you in advance.

Kindest regards

Krava

 

[rapsr59]

Hi Krava,

Since you have had success in accessing a single table using the method I suggested, I suspect your sql statement does not contain the proper syntax to access the second table.

Since you are certain that the second table exists then, perhaps, if you will post the sql you are using to access the second table we might be able to find a better syntax that will access the second table.

You should be able to access the second table even if it exists in a third db.

Please post the SQL statement so we can take a look at it..

Richard

 

[rapsr59]

Krava,

I'm not sure what kind of join you are using but...

The following SQL uses a LEFT JOIN to join two tables in a Back End db that I placed in a Front End db and it returns the records from the Back End db into the Front End db. The SQL statement was placed in a Query in the Front End db.

Since an error message stating that the second table could not be found, I suspect that the reference in the From Clause, to the second table location, was not stated properly in the From clause and perhaps Access was looking for the table in the Front End db.

In creating my SQL statement, I found it easier to create the SQL statement in the back end db...

SELECT Orders.OrderID, Orders.OrderDate, OrderDetails.OrderDetailID, 
OrderDetails.Product
FROM Orders LEFT JOIN OrderDetails ON Orders.OrderID 
= OrderDetails.OrderID
ORDER BY Orders.OrderID, OrderDetails.OrderDetailID;

... and then editing the SQL statement, I inserted the path to both tables in the From Clause...

SELECT Orders.OrderID, Orders.OrderDate, 
OrderDetails.OrderDetailID, OrderDetails.Product
FROM [COLOR=red][C:\Users\Richard\Documents\BackEnd.mdb].[/COLOR]Orders 
LEFT JOIN 
[COLOR=red][C:\Users\Richard\Documents\BackEnd.mdb].[/COLOR]OrderDetails 
ON Orders.OrderID = OrderDetails.OrderID
ORDER BY Orders.OrderID, OrderDetails.OrderDetailID;

... and then I placed the edited statement in a Query in the front end db and the records were returned in the Front End db exactly the way they were return in the Back End db.

As I mentioned in my last post, if the tables you are joining are in several dbs, you can join them together using this technique as long as you properly reference the location (path) of each of the tables.

I find setting the Path simply by pressing the Windows key on my keyboard, moving the mouse to Documents, right clicking the Back End db, selecting properties, and the Target, in the Properties dialog, will have the complete path. I then copy the path shown in the Target control, add the brackets [] and period (.) and wah la you have the location of the table -[C:\Users\Richard\Documents\BackEnd.mdb].

I hope this enlightens you on setting the proper path for each table you are accessing.

Please feel free to post any further questions you might have concerning this matter. If I can help I will. If not, I'm sure someone in the Forum will find an answer for you.


Regards,


Richard

 

[Krava]

Dear Richard!

Thank you very much for your helpful answer.
Well, I am probably going slightly mad, but I have tried this method last week and it did not work.
Now, I tried it again and it works.
So I am not really sure what is going on. But I suppose I have done some syntax mistake.
Have to see a doctor.

Anyhow- thank you very much one more time!

With kindest regrads

Andrej