Avatar of stopher2475
stopher2475
Flag for United States of America asked on

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.
    '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, True

Open in new window


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?
Microsoft AccessMySQL Server

Avatar of undefined
Last Comment
stopher2475

8/22/2022 - Mon
johanntagle

I can't help but wonder why people keep including MySQL Server zone for Access and MS-SQL questions....
ASKER CERTIFIED SOLUTION
Vadim Rapp

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Vadim Rapp

(sorry for the mistyped nickname above, after copy/paste and not deleting excess "Date:").
stopher2475

ASKER
I know there's a mySQL. I thought it had said MS SQL. I was following the Microsoft tree.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
stopher2475

ASKER
Thanks for the suggestion. I don't have permissions for Profiler will have to contact the admins.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
stopher2475

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.
stopher2475

ASKER
Table defiantly dropping. Some kind of problem apparently between SQL Server 2008 and Access 2007.
Setting up a limited user solved the problem.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.