Strange problems modifying my adp file

  • Thread starter Thread starter Charles Russell via AccessMonster.com
  • Start date Start date
C

Charles Russell via AccessMonster.com

I am have some very strange behaviors in my modifying my adp file. When
trying to open or create new file the screen flashes yet I do not have my
stored procedure screen I just go back to the object window. I am able to
modify the code on my forms. I am also able to trace code. Yet when I do
this changes to the tables are not seen. There are no error messages and I
have checked my permissions and they are fine. What is up I can see no
logical reason for this and have no idea how to fix it. I am desparte I am
against a deadline and I am dead in the water.
 
For the changes to the tables which cannot be seen: try to use the Refresh
option (F5) from the view menu when in the Tables Window section. Repeat
for the Queries window.

Where are you doing your changes to the tables? In Access or with the
Enterprise Manager of SQL-Server?
 
The refresh does not work either.

I can trace the following code and It appears to work right I get no error
messages when tracing. This was a quick despartion routine to try to get
the data where I need it. Whithin access I can not modify or change
tables, strored procedures or anything outside of forms or code.

Option Compare Database

Option Explicit

Sub FillITXP()
Dim cnn As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
Dim strQry As String
Dim strEmpID As String

cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;" _
& "Initial Catalog=SkillInvSQL;Data Source=xxxxx"
'xxxxx represents the server name.

rst2.Open "Select Distinct EmpID,SrcTbl From dbo.tblSurveyResponses
Order By EmpID", cnn
If Not rst2.EOF Then
rst2.MoveFirst
While Not rst2.EOF
rst1.Open "Select EmpID, ITXP From tblNameTitle WHERE EmpID =
'" & rst2.Fields("EmpID") _
& "' Order by EmpID ", cnn, adOpenDynamic, adLockOptimistic
strEmpID = rst2.Fields("EmpId")
While (strEmpID = rst1.Fields("EmpId")) And (Not rst2.EOF)
rst1.Fields("ITXP") = rst1.Fields("ITXP") & rst2.Fields
("SrcTbl") & ","
rst2.MoveNext
If Not rst2.EOF Then
strEmpID = rst2.Fields("EmpID")
End If
Wend
If Not rst2.EOF Then
rst1.Fields("ITXP") = Left(rst1.Fields("ITXP"), Len(rst1.Fields
("ITXP")) - 1)
Set rst1 = Nothing
Set rst1 = New ADODB.Recordset
End If
Wend
End If
cnn.Close
End Sub
 
I have figured a work around using the execute method on the command
object. I still would like to know why I can't create or modify stored
procedures in Access.
 
Back
Top