Access FE with SQL 2005 BE!

[smbarney]

Please help me. I have spent two days searching this forum and others looking for the answers to the following questions. This is driving me nuts.

Environment: Access 2003 frontend with MS SQL 2005 backend

Questions:
1. Using VBA, how do I use a server-side query to control a form?

2. Using VBA, how do I set the recordset of a form so that users can edit the values?

2. How do I pass a variable from Access (such as a text box or combo-box value) to SQL so SQL can use the value in a query?

3. Is there a way to have a form controlled by a query (or something else) that is dynamic? Meaning, depending on the user logged in, the form displays a different recordset.

If someone could provide me sample code of the above, and some good advice on best practices, I would be grateful beyond words.

Thanks

 

[RodgerDJr]

Are you having issues? I have not done this but I do work with SQL 2005 and Access? I might be able to help.

 

[SQL_Hell]

Are you using .mdb or .adp?

 

[smbarney]

I am using mdb. The database is already running using just access for its backend. I took a copy of the db and uploaded the tables to a SQL 2005 server. That worked fine. Now the issue is setting the record set on the forms (most of the forms require some "where" statement). I was trying to avoid using linked tables as I understand that they are not as fast. In addition, several of my forms run on queries and some of them need to be updateable.
Thanks

 

[smbarney]

Here is an example of one of the scripts I am working with:

Private Sub Form_Open(Cancel As Integer)
Dim strConnection, conn, rs, strSQL

strConnection = "Provider=SQLOLEDB; Data Source=myserver; Initial Catalog=myTable; User ID=UserNAme; Password=UserPW"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection

Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM IntakeMain WHere IntakeID = 5"

conn.Close
Set conn = Nothing

End Sub

I've placed this in the On Open Event. When I try and open the form, I get a Run-time 424 error: "Object Required" VB highlights the line: "Set conn = Server.CreateObject("ADODB.Connection")" as having an issue.

Basically, I want this code, when the form is opened, to set the recordsource on my form to IntakeMain on SQL Server. Users do not need to go forward or back a record but they do need to be able to update the record they are on.

This just seems so complicated. Please help....

Thanks

 

[boblarson]

Instead of

Set conn = Server.CreateObject("ADODB.Connection")

do this:

Set conn = New ADODB.Connection
conn.Open strConnection

 

[Darth Vodka]

why can't you bind the form to a table or a pass through query?

 

[lala]

Are you having issues? I have not done this but I do work with SQL 2005 and Access? I might be able to help.

can you help me then?
noone else can it seems and i'm having a problem with Access 03 and SQl 05

http://www.access-programmers.co.uk/forums/showthread.php?t=157799


thank you

 

[boblarson]

I'll bet that part of the problem is the driver you are attempting to use. Perhaps you should us an ODBC connection instead of OLEDB and that would be SQL Native Client as the driver for SQL 2005 (unless you are on Windows Vista which would be just SQL Server)

 

[RodgerDJr]

As Bob stated above when not connect to the tables via an ODBC connection? Are you trying to open a form and at that time create the record source? So in your tables you have nothing?

 

[lala]

i tried both, SQL server and SQL Native Client

and again, i did all the connections initially the same and 4 computers are horribly slow

and then i started playing with the connection and tried native client and it didn't help

the only way i was able to get it to work was by stripping all conditional formatting

but again, on the other 6 computers it works fine with the conditional formattiing

 

[lala]

no, all my tables are linked to SQL via ODBC data sources in Control Panel
dp you know what i'm talking about?

administrative tools, data sources (ODBC)

 

[boblarson]

no, all my tables are linked to SQL via ODBC data sources in Control Panel
dp you know what i'm talking about?

administrative tools, data sources (ODBC)

Yes, I know what you are talking about, but WHICH driver did you select to use?

 

[lala]

originally sql server, then trying to fix the 4 non working, SQL native client

 

[lala]

the only way it works on those computers is if i strip all conditional formatting, and still, slower than others WITH conditional formatting

this is sad, it's slower than when it used to be on Access BackEnd

 

[lala]

i just saw this on one of the forums, answering a similar problem for someone, what does he mean?

If you're running local queries in Access against linked tables/views then you're basically having to transfer the contents of the tables locally to have Access perform the query rather than let SQL Server do the work and only return the data that you're actually interested in.

 

[boblarson]

i just saw this on one of the forums, answering a similar problem for someone, what does he mean?

Perhaps this will help:
http://www.access-programmers.co.uk/forums/showpost.php?p=716762&postcount=16

 

[lala]

thank you, it does, but it was i think you, Bob, that said this in response to his post

08-06-2008, 09:49 AM
boblarson
Access Casualty Join Date: Jan 2001
Location: Oregon, United States
Posts: 12,126


Quote:
Originally Posted by tehNellie

Slow network connection and/or large tables that you're linking to?

If you're running local queries in Access against linked tables/views then you're basically having to transfer the contents of the tables locally to have Access perform the query rather than let SQL Server do the work and only return the data that you're actually interested in.

Actually, that isn't necessarily true. If you are not doing any calculating within the query, Jet will optimize the query it sends so you don't actually pull everything. Pat Hartman has some posts here somewhere about all of that.

Some slowness can also be experienced if you have a bunch of combo boxes, listboxes, etc. that are being loaded or refreshed always. I don't have time right now to go find them but there are some good articles about enhancing speed with Access/SQL Server. I would try googling for them.

 

[RodgerDJr]

Is this when you open a table or query?

 

[lala]

that it slows down? a form