How Best to Append MS Access table to Remote MS SQL Table

I have a local MS Access table used by inspectors in areas without Internet.  I need to append 250 fields to remote MS SQL table when they return to office without duplicating current ID numbers.  Must I list all 250 fields or can it be accomplished using *?  I've attempted various forms of the following without success:
Following the connection string:

strSQL1 = "INSERT INTO dbo.dams_inspection_" & PCUser & "" _
         & "SELECT * FROM dbo_dams_inspection;"
                     
    Set td = CurrentDb.TableDefs("dbo_dams_inspection")
    DoCmd.RunSQL (strSQL1)

--followed by other procedures for retrieving current data from remote server
D.L. WyerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
You have to list all 250 columns if you go directly table to table.  You can create a query that selects only the other columns from the local table (you need each column but at least you can do it easily with QBE but selecting all of them and then deleting the one you don't want).  Then use the select query in the SELECT clause of the append query.
0
Dale FyeCommented:
First, why do you have 250 fields in a single table?  It sounds like you have an extremely denormalized (almost spreadsheet) database table.

When I have to deal with this type of "home grown" replication, I generally use two fields integer fields (userID and UserRecID), neither of which is an autonumber as the primary key of my tables.  This way, I can generate the "next" UserRecID based on a query and don't have to worry about creating duplicate records.

It appears that you have taken a different tack, creating and using a separate table for each user.  This will be problematic when you want to create queries or reports that span multiple users.  It is far more preferable to have a single table which contains a PCUser column (or as in my example above UserID).

You could also build your SQL string programmatically, looping through the recordset fields collection to build the SQL.

Set rs = currentdb.OpenRecordset("YourTable")
for each fld in rs.fields
   strFields = strFields & ", [" & fld.Name & "]"
next
strFields = Mid(strFields, 3)

strSQL = "INSERT INTO dbo.dams_inspection_" & PCUser & "(" & strFields & ")"  _
         & "SELECT " & strFields & " FROM dbo_dams_inspection;"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
D.L. WyerAuthor Commented:
I inherited a 10-year old web based application that they now want converted to desktop without losing use of the old data.  The app is to run on SurfacePro 3, so each field is populated when touched.  This is for inspecting the state's dams, and most do not have Internet access.  There are currently 250 points of inspection and they are all in one table.  This is better suited for a C#/SQL Server Express solution, but I've not written a line of C code since the 70's.  As for the PCUser, there also has to be a type of workflow approval system where the inspector submits his/her inspection and the district supervisor reviews it and either rejects it and sends it back to the inspector or approves it and sends it on to the state director who sends it back or gives final approval.  At that point it can be appended to the main database.  Additionally I have to add FileStream support for the mammoth amount of pictures they take at each phase of inspection (hence SQL Server Express instead of LocalDB).  I had hoped MS Access would provide a quick solution since they want this yesterday, but it has not been so accommodating.  And of course, being a state agency, there is no money in the budget for outsourcing, so I'm pretty much stuck with it.
0
PatHartmanCommented:
It is pretty quick for me.  How long can it take to double click on the table - access adds all the fields to the grid.  Then you delete the ID field.  Save the query and use that query in your append query.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.