Access to SQL Server: Form data sources

[zeroinsum]

I'm in the midst of my first Access to SQL Server migration, and I have a question about form data sources.

I have a form that is used for entering and updating orders. In its, all-Access incarnation, the datasource for the form was a fairly straightforward query that worked more or less exactly as it should.

I've taken that query and converted it to a SQL View, and it works for the most part, but there's one key difference.

With the old, in-Access query (which still works, but it seemed like I should move as much as possible to the server side, so I'm mucking about with something that isn't really broken), if I, for instance, select a given customer for the order, that customer's address, phone number, etc. all immediately populate on the form.

When I use the view, however - which is essentially identical to the query, referencing the exact same fields on the exact same tables - if I select a customer, none of that populates until the form is refreshed.

Now, this isn't a huge problem. I could script my way around this, no problem. However, I'm very interested in doing this not just in a way that "works", but in a way that is objectively correct, and it seems to be that I'm probably doing something wrong here, and that scripting a refresh into the form would merely be a bandaid fix to a bigger problem.

Any help?

 

[seaport]

Please post the code of "selecting a customer". If you simply using the DAO recordset's findfirst method, there should be no difference in using access queries or linked tables from SQL server views, at least from the perspective of viewing data (no updating).

 

[zeroinsum]

I don't have the code handy (am at home, now), but it's pretty straight forward stuff. I have a pretty basic form using a query as a datasource. The query consists of a handful of fields from the order table and customer table, linked together by the customerID.

It's actually, having tinkered with it further, not a function of forms at all.

As a for-instance, I just made a query consisting of those two tables and only 3 fields: TblOrders.OrderID, TblOrders.CustomerID, and TblCustomers.Address.

If I just open the query itself, go to the CustomerID field, type in any old customer ID, and select address field, the address field will automatically populate.

If I take the code from this query, cut and paste it into the form of a SqlSvr view, and attempt the same thing (with the view linked into Access), the address won't populate immediately - it will remain blank until I do something to save the entire record (e.g., select a different record entirely).

 

[zeroinsum]

...and, actually, having tinkered with it further, it seems like this is simply how views work in general. I'm attaching an image to this to further explain what I'm getting at.

The view shown is:

SELECT     dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Customers.CustomerName
FROM         dbo.Orders INNER JOIN
                      dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID

Obviously, there is only one field for valid data entry in this views: CustomerID.

Now, if this were in access, and the above were being used as a form datasource, entering a customer ID would automatically and immediately populate the CustomerName field. As a view, it does nothing of the sort - it appears to be batched until the update is executed, at which point everything fills in (as seen in the image).

This is problematic for me, because the user needs to see certain linked information from the customer table during data entry.

So, what gives? Is this not a task I should be using Views for? Should I just stick with Client-side queries and linked tables? Is there some other structure I should be using (I haven't even glanced at stored procedures yet...)?

I'm 100% certain that there is some way to achieve the result I'm looking for, but I haven't the faintest idea how to reach it.

 

[seaport]

I understand what you want to do. I do not think it can be done. SQL server is intended to provide performance and capacity, not necessarily the convenience for Developers. In a pure Access environment, retrieving and updating data can be done by Access automatically. Access automatically refreshes data. But when the backend database is SQL server, this kind of automation is not available. I believe it is to reduce the burden of the server.

Actually, I would prefer hand-code the reading and writing to the server if I were given a choice.

 

[danvlas]

SQL Server works really different from Access, it is a real server and fetches data only when requested so.
What really happens is that you fill in some boxes in an interface. The values are transmitted to the server through a 'Command' and it's only then that the action is executed.

To fix this issue, you'll need to dive into stored procs. For instance:

Create Proc GetTableRow (@ID int)
As
Set nocount on
Select * from TableName Where PrimaryKey=@ID

Then I create a single form having the textboxes named exactly like the field names.

In the AfterUpdate event of the textbox showing the ID:

Private Sub IDField_AfterUpdate()

Dim rst as New ADODB.Recordset
Dim cmd as New ADODB.Command
Dim fld As ADODB.Field
Dim ctr As Control


With cmd
   .ActiveConnection=CurrentProject.Connection
   .CommandText = "GetTableRow"
   .CommandType= adCmdStoredProc
   .Parameters.Refresh
   .Parameters("@ID")=Me.IDField
   Set rst = .Execute
   For Each fld in rst.Fields
      For Each ctr in Me.Controls
         If fld.Name=ctr.Name then
             If rst.EOF then
                 ctr=Null
             Else
                 ctr=fld
             End If
             Exit For
         End If
      Next
   Next
End With

End Sub

This way, whenever you enter the ID, the controls on the form will be filled in. You may even store the original record (rst) for future use.

I think you'll have to learn to do things the SQL Server way instead of Access. It's a one-way road, believe me.

HTH


Daniel Vlas
Systems Consultant

 

[zeroinsum]

Alright, thanks. I don't mind going to additional lengths to secure the behavior I'm looking for, I just wanted to make sure, before doing so, that I wasn't doing something terribly wrong.