I had a import method working for around 2 years. I would get an extract in Excel 2007 format and would use MS Access TransferSpreadsheet method to import it onto a linked table(Resides on MS SQL Server). This way a user could do it and not rely on me doing it having Management studio. Then my employer decided to change my data source and structure. So I rebuilt the system data structure to accommodate the new data. Since I have had nothing but problems with the end user import. It works fine with me doing an import directly with the server but not through the TransferSpreadsheet method. When running that command it gives me a generic you cannot record your changes because a value you entered violates the settings defined for this table. So I changed the extract format to a csv and then to a tab delimitated to no avail same result. (Just thought it might have been a funky excel data change somewhere). Like I said it can still be imported directly from the server but that is not the solution I like.
Using Access as my import method or the import wizard in SQL server is the only way I have imported data into SQL Server. I was wondering if any of you could steer me in the correct direction on either why all of a sudden why my transferspreadsheet stopped or another method to import aprox 100,000 rows of data to the server instead of inserting row by row.... BTW I cannot do a Bulk insert command either(DBA restricted that. Working on getting that changed as well.)
Good thought but yes... The odd part is that I can do the import directly using management studio. I am only unable to import via the transferspreadsheet method. I might be wrong but if I had constraint issues it would crop up during the import in management studio.
Yes I have relinked the table. Actually I had created a new table and brought it in from base. I just do not know if access would be putting a constraint on artificially. Does anyone know a way to import using a vbscript with no bulk insert. I am negotiating now to see if we can get that enabled by chance.