Unanswered: Need suggestions on issue surrounding Access
I have a question and want to know if its possible since I am rather new to programming side of things. Here is my situation:
There is a database here at my work that I have to query via excel to get infomation. I do this via ODBC Connection. I do this via Excel (Microsoft Query). I then am using this information in Infopath which needs the data to be in Access or SQL Server but dont have access directly to the server so have to put in Access.
I would need a macro/code for running the SQL in Excel which queries the data from the SQL Server database.
I would need Access macro/code to automatically import the data from Excel.
Then I would like to automate this on a daily basis so then I would a batch file correct?
Basically I want the Access Database to be updated automatically everyday from the SQL that I do in Excel which comes from the Server, so that the Infopath form has the most up-to-date information everyday.
Bascially this is what happens now:
SQL Server--->Excel Query--->Access--->Infopath
The main thing is that Infopath has to be used with Access. Yet I have to SQL Query the Server for the data I need.
How can get the data from the Server to Access the easiest and have this process be automated?
Is this something that can be accomplished? Or...does anyone have any ideas of how to do this differently (maybe easier) but accomplish the same ends?
Thank you for any help and insight and hope I wasnt confusing...
Since this is revolving around Access thought this forum would be appropriate.
I suspect the easiest way to get data from SQL Server to Access, including automating it, would be a DTS package in SQL Server (more info in BOL). Alternatively, you could create a process in Access to import the data, and run that on a schedule.