August 9, 2007 at 11:18 am
aljtdj thanks for the help, but I will try to implement the suggestions made by Steve. So Steve you are in control now, I will do as you say. Like I told you before, I am new on this so go easy on the instructions.
Thanks
August 9, 2007 at 11:54 am
FYI, the process I am referencing builds the insert SQL statements on the fly and matches field names as part of the process. For the software company I used to work for, I used the code to upgrade our application data from one version to the new version.
You create a table with all of the table names to be updated (which can be done automatically in Access). Your destination tables (SQL Server) should already be in the database. You link in all of the source tables(Excel in this case). You then use this loop to create a SQL string that will INSERT the data from the old table to the new one. Part of the SQL string creation process actually loops through each table and does a field match so you only update the fields that exist in both tables. NOTE: the source and destination tables have to be named the same. The process is hard coded with a 1 after the source table because Access automatically assigns a 1 to a dupe object.
I have used this process to upgrade data ---NOT TABLE INFO OR DATA TYPES, data conversion had already been done--- from Access to MySQL and SQL Server. The type of table connection is not relevant to using this process. However, using Excel tables is problematic because of the way the column names are treated when linked. You would probably have to build a text file specification for each spreadsheet.
Newton, DBA's and programmers generally look at processing data differently because of the tools available to them. Programmers can create loop processes for this type of issue because data sources are just 'sources' and programmers generally don't care about the platforms on which the data resides.
Best of Luck!
TJ
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply