upsized SQL Server aplication issues

[SimonT]

Hi Guys,

I am in the process of upsizing a MS Access application to a Access FE and
SQL BE. (MS Access 2007 SQL2005)

I used the MS upsize wizard to convert all the tables to SQL format.

I have been going through the application and updating the linked tables to
the SQL database and updating any control source queries to to new SQL
tables.

One of my current issues, and there are many, I have a form for producing an
invoice of items. On this form I have a button that previously ran a query
to update stock levels in an inventory table.

I have updated the script to use the new SQL tables but the script no longer
works with the SQL tables. I am assuming therefore I will need to change
some of the settings in the query to support the new method, but could do
with help and guidance on this, this is my vba code:

Private Sub buttonClose_Click()
Dim rsOrderLineItems As DAO.Recordset
Dim rsCSInventory As DAO.Recordset

Dim strQty As String
Dim strWeight As String
Dim strSQl As String

Dim ctlQty As Control
Dim ctlWeight As Control

Me.txtAmt = Me.frmOrderItems!txtTotal
Me.txtbalance = Me.frmOrderItems!txtTotal

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Set rsOrderLineItems = CurrentDb.OpenRecordset("dbo_orderlineitems",
dbOpenDynaset)
Set rsCSInventory = CurrentDb.OpenRecordset("dbo_tblinventory",
dbOpenDynaset)

DoCmd.SetWarnings False

strSQl = "UPDATE dbo_tblinventory " & vbCrLf
strSQl = strSQl & " INNER JOIN dbo_orderlineitems " & vbCrLf
strSQl = strSQl & " ON dbo_tblinventory.productID =
dbo_orderlineitems.productID SET dbo_tblinventory.SumOfqty =
[dbo_tblinventory]![SumOfqty]-[dbo_orderlineitems]![qty]" & vbCrLf
strSQl = strSQl & " WHERE
(((dbo_orderlineitems.custID)=[forms]![frmOrderPlacement]![txtcustid]) " &
vbCrLf
strSQl = strSQl & " AND
((dbo_orderlineitems.orderID)=[forms]![frmOrderPlacement]![orderid]));"

DoCmd.RunSQL strSQl

rsCSInventory.Close
rsOrderLineItems.Close

Set rsCSInventory = Nothing
Set rsOrderLineItems = Nothing

DoCmd.Close
End Sub
Any suggestions and advise most welcome, if I am making a blinding obvious
mistake, please be gentle I am new to SQL

I am posting in a couple of forums as not much activity in odbcclientsvr and
wonder which is the correct one

Many thanks
Si

[Stefan Hoffmann]

hi Simon,

On 17.03.2010 11:11, SimonT wrote:
> I used the MS upsize wizard to convert all the tables to SQL format.

I've done this also several times without having much trouble.

Ensure that all upsized tables have now the additional TIMESTAMP column.

> I have been going through the application and updating the linked tables
> to the SQL database and updating any control source queries to to new
> SQL tables.

This is an unnecessary and error-prone task. Instead of changing all
queries:

1. Turn off Auto-Name Correction.
2. Rename your tables, strip the schema prefix.

> I have updated the script to use the new SQL tables but the script no
> longer works with the SQL tables. I am assuming therefore I will need to
> change some of the settings in the query to support the new method, but
> could do with help and guidance on this, this is my vba code:

What error do you get?

mfG
--> stefan <--

[SimonT]

Hi Stefan,

I did select the TIMESTAMP and it has been added to some tables, but not
all, do I need to add this to all tables, or just those we are likely to
update?

I do not have Auto Name selected in Access, or are you referring to SQL
server? if so where do I do that?

How do I remove the scheme prefix?

In relation to the VBA code posted with the question, I do not get an error,
just nothing happens, the button action does not even close the form?

Regards
Si

"Stefan Hoffmann" <ste...@ste5an.de> wrote in message
news:OAMSw1bx...@TK2MSFTNGP05.phx.gbl...

[Stefan Hoffmann]

On 17.03.2010 13:30, SimonT wrote:
> I did select the TIMESTAMP and it has been added to some tables, but not
> all, do I need to add this to all tables, or just those we are likely to
> update?

While it is sufficient to have it it these tables, I'd prefer it in all
tables mainly to have a kind of symmetric base table structure.

> I do not have Auto Name selected in Access, or are you referring to SQL
> server? if so where do I do that?

I meant the Auto Name Correction feature you will find in the options
dialog for the current database.

> How do I remove the scheme prefix?

Either manually or by code:

Dim db As DAO.Database
Dim td As DAO.TableDef

Set db = CurrentDb
For Each td in db.TableDefs
If Instr(td.Name, "dbo_") = 1 Then
td.Name = Mid(td.Name, 4)
End If
Next td

> In relation to the VBA code posted with the question, I do not get an
> error, just nothing happens, the button action does not even close the
> form?

First of all, if you want to close a specific object, name it:

DoCmd.Close acForm, Me.Name

Now for the errors: You disabled the warnings. This may suppress some
messages.

I would rewrite my procedure:

Private Sub buttonClose_Click()

Dim sql As String

' Me.txtAmt = Me.frmOrderItems!txtTotal
' Me.txtbalance = Me.frmOrderItems!txtTotal
' Use field assignments instead, e.g.

Me![Amount] = frmOrderItems!txtTotal.Value

If Me.Dirty Then
Me.Dirty = False ' Saves pending changes.
End If

sql = "UPDATE dbo_tblinventory I " & _
"INNER JOIN dbo_orderlineitems OI " & _
"ON I.productID = OI.productID " & _
"SET I.SumOfqty = I.[SumOfqty]-OI.[qty] " & _
"WHERE OI.custID=" & Forms!frmOrderPlacement!txtcustid & " " & _
"AND OI.orderID=" & Forms!frmOrderPlacement!orderid & ";"

CurrentDbC.Execute sql, dbFailOnError
If CurrentDbC.RecordsAffected > 0 Then
MsgBox "Changed " & CurrentDb.RecordsAffected & " records."
End If

Exit Sub

LocalError:
MsgBox Err.Description, vbCritical

End Sub

Place this in any standard module and use CurrentDbC instead of CurrentDb:

Private m_CurrentDb As DAO.Database

Public Property Get CurrentDbC() As DAO.Database

If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If

Set CurrentDbC = m_CurrrentDb

End Property

mfG
--> stefan <--

[SimonT]

Ok, Rename in FE not BE....I was really struggling with that !!

I have now renamed the tables as suggested and before I go into the changes
you suggest below, re my orders and stock updates. When I go to the invoice
form, the subform for the line items does not show?

However, if I look at the query that the subforms I can see the table data.

The form is linked as before, not altered anything, and if I test using the
Access BE tables, the subform displays with no problems. Any suggestions?

Regards
Si

"Stefan Hoffmann" <ste...@ste5an.de> wrote in message

news:%23u%235yPdx...@TK2MSFTNGP04.phx.gbl...

[Stefan Hoffmann]

hi Simon,

On 17.03.2010 16:50, SimonT wrote:
> The form is linked as before, not altered anything, and if I test using
> the Access BE tables, the subform displays with no problems. Any
> suggestions?

The problem is: your code should work at the first glance. So you should
consider 'rebuilding' the new front end.

Take a copy of your old front-end which was using the Access back-end.

Turn off the 'Name AutoCorrect' feature under File\Options\Current Database.

Exchange the linked tables so that they now point to the SQL Server
beack-end.

Check all tables and stored queries.

After that test all forms, start with your master data forms.

mfG
--> stefan <--

[SimonT]

Hi Stefan,

Thanks for that, I am using access 2007, so assume 'NameAutoCorrect' feature
you are talking about are via Access Options>Current
Database>NameAutoCorrectOptions, this is deselected.

The frustrating thing about this is many of the forms work ok, just some
aren't ...arg

I have created a view, to use for the lineitems subform, and am getting
strange errors, i.e. when I enter a quantity the value changes after tabbing
through the field???

I have checked the form using the Access BE tables and everything works
fine, with 50+ forms, I don't relish starting again!!

Regards
Si

"Stefan Hoffmann" <ste...@ste5an.de> wrote in message

news:%23XE9XQf...@TK2MSFTNGP05.phx.gbl...

[Stefan Hoffmann]

hi Simon,

On 17.03.2010 19:06, SimonT wrote:
> Thanks for that, I am using access 2007, so assume 'NameAutoCorrect'
> feature you are talking about are via Access Options>Current
> Database>NameAutoCorrectOptions, this is deselected.

Correct.

> The frustrating thing about this is many of the forms work ok, just some
> aren't ...arg

Then you have to dig into these problems. Compare the code behind of
these forms maybe can identify some major differences.

Have you tested my proposed rewrite of your button event? DoCmd may hide
under some circumstances error messages.

mfG
--> stefan <--