X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Split Database With SQL 2014 Backend, Access 2013    
post Feb 13 2018, 02:51 PM

Posts: 72
Joined: 18-July 07
From: Leduc, Alberta

Good Afternoon,

I was using this code when the dbase was not split and it worked like a charm. The ODBC connection is causing me grief, any advice would be appreciated.

Private Sub PartFlip_Click()

Dim strMsg As String
Dim iResponse As Integer
Dim TempPartID As Integer
Dim TempModID As Integer
Dim PartDescription As String
Dim PartService As String
Dim PartTemp As Boolean
Dim PartSchedule As String
Dim PartSize As String
Dim PartMaterial As String
Dim Cancel As Boolean
Dim BOM As Integer
Dim strInput As String
Dim strInput2 As String
Dim strMsg2 As String
Dim strMsg3 As String
Dim strInput3 As String
Dim strMsg4 As String
Dim strInput4 As String
Dim strMsg5 As String
Dim strInput5 As String
Dim strMsg6 As String
Dim dbs As DAO.Database
Dim rsTable As DAO.Recordset
Dim rsQuery As DAO.Recordset
Dim rst As DAO.Recordset

PartDescription = Me.Description
PartService = Me.Service
PartTemp = Me.LowTemp
PartSchedule = Me.Schedule
PartSize = Me.Size
PartMaterial = Me.Material
TempPartID = Me.ID
TempModID = Me.ModID1
BOM = Me.Text113
strMsg2 = "Please enter part quantity."
strInput = InputBox(Prompt:=strMsg2, title:="Quantity")
strMsg3 = "Please enter part price."
strInput2 = InputBox(Prompt:=strMsg3, title:="Price")
strMsg4 = "Please enter part tag."
strInput3 = InputBox(Prompt:=strMsg4, title:="Tag")
strMsg5 = "Please enter part make."
strInput4 = InputBox(Prompt:=strMsg5, title:="Make")
strMsg6 = "Please enter specific Part dimensions."
strInput5 = InputBox(Prompt:=strMsg6, title:="Dimension")
Set dbs = CurrentDb
Set rsTable = dbs.OpenRecordset("QuoteDetails", dbOpenTable)

   ' Specify the message to display.
strMsg = "Do you wish to insert this record? " & Chr(13) & Chr(13) & "Quantity: " & strInput & Chr(13) & "Price: $" & strInput2 & Chr(13) & Chr(13) & "Tag: " & strInput3 & Chr(13) & "Make: " & strInput4 & Chr(13) & Chr(13) & "BOM#: " & BOM + 1 & Chr(13) & "Service: " & PartService & Chr(13) & "Low Temp: " & PartTemp & Chr(13) & "Part Schedule: " & PartSchedule & Chr(13) & "Part Size: " & PartSize & Chr(13) & "Material: " & PartMaterial & Chr(13) & "Dimension: " & strInput5 & Chr(13) & "Part: " & PartDescription & Chr(13) & Chr(13) & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

   ' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")
   ' Check the user's response.
   If iResponse = vbNo Then
      ' Undo the change.
Exit Sub

      ' Cancel the update.
      Cancel = True
End If

rst!ModID = Me.ModID1
rst!PartID = Me.ID
rst!BOM = Me.Text113 + 1
rst!Quantity = strInput
rst!Price = strInput2
rst!Tag = strInput3
rst!Make = strInput4
rst!Dimension = strInput5

End Sub[u][/u]

Set rsTable = dbs.OpenRecordset("QuoteDetails", dbOpenTable) Is where the error begins....
Go to the top of the page
post Feb 13 2018, 03:23 PM

Posts: 72
Joined: 18-July 07
From: Leduc, Alberta

Jeepers.... I had a typo in the code that Pointed to non-existent table. The code works to some degree.

Another problem I am finding is that I can only delete certain records as access says: Another user is in the record which is preventing record modification. This only applies to records I created using the code above? I am the only one using this database....
Go to the top of the page
post Feb 13 2018, 05:46 PM

Posts: 2,397
Joined: 27-February 09

I think I read your code carefully enough, and I didn't see the close/destroy that goes with this code:

Set dbs = CurrentDb
Set rsTable = dbs.OpenRecordset("QuoteDetails", dbOpenTable)

After you're done with the table, you need:

Set rsTable = Nothing
Set dbs = Nothing

Basically, if you open an object in code, close it. If you have references to objects, set all those to Nothing.
Go to the top of the page
post Feb 13 2018, 08:25 PM

UA Admin
Posts: 32,115
Joined: 20-June 02
From: Newcastle, WA

"The ODBC connection is causing me grief, ..."

I see nothing there about the ODBC connection itself. What kind of grief is it causing? Does it work too slow? Does it fail? What DOES happen? What do you WANT to happen?

"The code works to some degree." What degree would that be? And what part fails? We have to rely on you to explain.

"I can only delete certain records ..."

The error message seems to indicate, though, a fairly typical kind of problem. When you have a form bound to an ODBC-linked table, and try to modify (add, update or delete) records in that same table in VBA, Access will not allow the code update because the record is currently locked in the form.

There are probably ways to get a result, but let's start with details about WHEN, WHERE and HOW you tried to delete those records.
This post has been edited by GroverParkGeorge: Feb 14 2018, 10:45 AM

Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    21st March 2018 - 03:59 AM