Update SQL Server from Access

[Rowen]

Hi, I'm hoping someone can help. I'm setting up an access application to work on a tablet PC. This is so that the users can update the application with data while their out and about. (Instead of the current system, where they fill in a piece of paper and this gets updated to the system later.) When the users come back to the office, they then plug the machine into the network and press a button on an access form to copy the data over from the access tables to tables in SQL Server.

The problem is that I'm not quite sure how to do the code in vba to connect to SQL Server and insert the data into the SQL Server tables. I have done some googling but I can't really tell if what I've found is really any help, so some pointers would be much appriciated.

Thanks.

 

[DCrake]

If I were you, or in the same situation as you, My approach would be to create a DTS package in SQL Server that performs all the work you need to do and fire that. So instead of pushing the data into SQL your pulling the data from Access.

David

 

[Rowen]

I don't actually know much about SQL Server, I haven't yet really been able to use it. How would I set up the DTS file? Or could you explain a bit more about it. It's just the users I have are really not tech savvy and I want it all to be just a button press for them.

This maybe a silly questions but if I can do this DTS file, does that have to be run from SQL Server? (or is there another way they could run it, via a small standalone programme? or even calling it from access itself?) The SQL Server database itself uses Access as a front end.

 

[Rowen]

Well no one's replied to my other question so I'm wondering if I could perhaps get some help on this, I'm going for an ODBC connection (as my knowledge of SQL Server is fairly non-existant at the moment) even so I've never used ODBC connections before either, and I'm wondering if I could get a yay or nay on whether what I want to do will work, and possibly some advice.


If I open an ODBC connection to the database, and then use DoCmd.RunSQL statments to insert the data is this likely to work? At the moment I have the following code: (I haven't been able to test it as I don't yet have access to the SQL Server so I can't set up any temporary tables to test this.)

oConn.Open "ODBC;Driver={SQL Server};Server=ServerName;" & _
"DATABASE=DatabaseName;UID=" & Me.txtUserName.Value & _
";PWD=" & Me.txtPassword.Value

DoCmd.RunSQL "INSERT INTO sqlserver.databasename.dbo.tblEstateInspections (StairID, InspectionDate, TRAPresent, " & _
" LobbyStairs, WallsCeilings, CommunalWindows, CommunalLighting, Grounds, ChuteRoom, EntryPhone, " & _
" GroundsMaintenance, Detritus, Comments, OverallGrade, ASBInBlock, AbandondVehicle ) " & _
" Select * From servername.onSiteInspections.dbo.EstateInspections; "

Although one thing I'm not sure about is that if the data is on the tablet, what the server name would be for that? Or would it just be the drive name such as the C drive?

 

[Tdgu]

Hello,

Regarding your first question, updating a SQL server from Access is possible. You can use different tools including ETL tools. They will perform the job for you. You can look at ETL tools.

Talend Open Studio is an open source ETL tool for data integration and migration experts. It's easy to learn for a non-technical user. What distinguishes Talend, when it comes to business users, is the tMap component. It allows the user to get a graphical and functional view of integration processes. For more information: http://www.talend.com/
​