Loading Table Disappearing
I have an access .adp file that loads data to a sql server database.
I use a function that uses the transfer spreadsheet function like so.
Basically I give my function a file path and a data type and it loads the data into the appropriate loading table.
After recently moving this database from a 2000 to a 2008 SQL server I'm finding the loading table will randomly disappear and I have to recreate it. I'm not dropping it anywhere. Is there some kind of incompatibility with 2008 that would cause the table to drop?
I use a function that uses the transfer spreadsheet function like so.
'Delete prior data for this date.
CurrentProject.Connection.Execute "Delete From dbo.tblWrk_" & sDataType
SysCmd acSysCmdSetStatus, "Importing worksheet..."
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblWrk_" & sDataType, sFullName, TrueBasically I give my function a file path and a data type and it loads the data into the appropriate loading table.
After recently moving this database from a 2000 to a 2008 SQL server I'm finding the loading table will randomly disappear and I have to recreate it. I'm not dropping it anywhere. Is there some kind of incompatibility with 2008 that would cause the table to drop?
johanntagle
I can't help but wonder why people keep including MySQL Server zone for Access and MS-SQL questions....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
(sorry for the mistyped nickname above, after copy/paste and not deleting excess "Date:").
ASKER
I know there's a mySQL. I thought it had said MS SQL. I was following the Microsoft tree.
ASKER
Thanks for the suggestion. I don't have permissions for Profiler will have to contact the admins.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The admin says it was dropped by the user I had set up to run the access data project. None of my procedures or code drops the table so unless it's a bug a user would have to hit F11 to pull up the database window and manually delete the table. It's pretty wired. This isn't an enterprise application so I didn't have a big permissioning plan set up but I'll have to set up a new user with only read/write/exec permissions.
ASKER
Table defiantly dropping. Some kind of problem apparently between SQL Server 2008 and Access 2007.
Setting up a limited user solved the problem.
Setting up a limited user solved the problem.