I have just started using Sql Server 7 and am having a problem viewing the data that I upsized from Access 2000 or accessing it from working VB programs. I assume the data is in the database from the size of the file.
I upsized the data from Access 2000 to Sql 7 so I could include the indexes since there are may indices associated with each of the 67 tables in the database to handle our order entry, billing and inventory control. I like just import the data into SQL using DTS is there is a way to also import all the indices.
At the server using the Enterprise Manager I cannot view the table - the error message I receive is "An unexpected error happened during this operation." The VB program that is working with Access 2000 does not recognize the data either. The connection seems to be correct since I can add records to any of the tables but none of the SQL statements to retrieve records returns any records in the requested record set.
My connection string is:
Set mcnn = New ADODB.Connection
mcnn.ConnectionString = ConnectionString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=SalesInventSQL;Data Source=Green3"
mcnn.open
A sample to retrieve a recordset is :
Public Function CheckUser(UserID As String)
'Check user ID to determine if valid and allowed procedures
UserPermits = Space(15)
If OpenConnection() Then
Set usrs = New ADODB.Recordset
usrs.CursorType = adOpenKeyset
usrs.LockType = adLockOptimistic
usrs.Source = "SELECT * FROM Users " & _
"WHERE USID = '" & UserID & "'"
Set usrs.ActiveConnection = mcnn
usrs.Open 'Options:=adCmdFile
If usrs.RecordCount <= 0 Then
'Process user logon
End If
usrs.Close
End If
End Function
What have I missed in migrating the data or what am I not doing correctly to view and access the data?
I appreciate any and all help. Thank you in advance.
I upsized the data from Access 2000 to Sql 7 so I could include the indexes since there are may indices associated with each of the 67 tables in the database to handle our order entry, billing and inventory control. I like just import the data into SQL using DTS is there is a way to also import all the indices.
At the server using the Enterprise Manager I cannot view the table - the error message I receive is "An unexpected error happened during this operation." The VB program that is working with Access 2000 does not recognize the data either. The connection seems to be correct since I can add records to any of the tables but none of the SQL statements to retrieve records returns any records in the requested record set.
My connection string is:
Set mcnn = New ADODB.Connection
mcnn.ConnectionString = ConnectionString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=SalesInventSQL;Data Source=Green3"
mcnn.open
A sample to retrieve a recordset is :
Public Function CheckUser(UserID As String)
'Check user ID to determine if valid and allowed procedures
UserPermits = Space(15)
If OpenConnection() Then
Set usrs = New ADODB.Recordset
usrs.CursorType = adOpenKeyset
usrs.LockType = adLockOptimistic
usrs.Source = "SELECT * FROM Users " & _
"WHERE USID = '" & UserID & "'"
Set usrs.ActiveConnection = mcnn
usrs.Open 'Options:=adCmdFile
If usrs.RecordCount <= 0 Then
'Process user logon
End If
usrs.Close
End If
End Function
What have I missed in migrating the data or what am I not doing correctly to view and access the data?
I appreciate any and all help. Thank you in advance.
