October 27, 2011 at 12:07 pm
I know that importing data into an access database from SQL Server 2000/2005/2008 into Microsoft Access has probably been beaten to death many times over, but this is just a flat-out pain.
I have absolutely no control over the computers that run my Access database, zero, yet, I need to be able to get out and grab data from my SQL Server 2008 database. Without fail, someone on some PC will come up with some ODBC connection error or something else that is totally obscure.
This is essentially what I do. I have a form, and on a button click, it performs the following tasks: 1.) Gets the current DB using DAO.Database.CurrentDb (), 2.) Gets the connection string (stored in the Access database), 3.) Loops through all of the TableDefs putting its name into an array, 4.) Drops all of the data from the database tables using said array, 5.)Creates a temporary dbo_download_list table, 6.) Connects to my SQL Server 2008 database, 7.) Imports the data from dbo.tbl_download_list into a record set, 8.) For each record in that record set which contains table names, all of the data within those tables is dumped into a corresponding table in the Access database. 9.) Everything is cleaned up.
At first, I was thinking about just grabbing data from a Web service, but in order to do this with Access 2007, you need to install some extra extensions and unfortunately I do not have that luxury. This works, when it works, but I need it to work reliably across multiple networks and topologies.
I have my SQL Server setup on port 1633, so if this port is not open I can see some problems occurring when connecting. I was just thinking if there is a way that I could get away from instructing them to open a specific port by instead using a commonly open port like port 80. I am a web developer by trade, so I was also tossing around the idea of creating a webpage that just spit out the text file, then import the data that way, but I haven't been able to find any examples on how to do that with VBA.
Any ideas would be greatly appreciated.
Thanks,
Jim
October 28, 2011 at 8:57 am
SQL Server connectivity can be a major pain with Access applications. I presume there is a compelling reason for not using linked tables (the approach we normally use), instead of copying the data. The issue with firewalls and port blocking pretty much require that any workstation connecting to the database have some configuration work done. I would not switch to using port 80 however - there are too many malwares that use that port. If the data that is downloaded from SQL Server is always the same set of data at any given point in time, you might consider putting a master copy of the Access database on a network location and copy that to each of the local workstations as they need to refresh data. You could schedule a refresh of the master on an automated basis. Another option you might consider is using an ADP, which connects directly to SQL Server. Hope this provides some ideas for discussion.
Wendell
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
October 28, 2011 at 12:27 pm
This is the kind of stuff I used to do in 2003 ofr these kinds of things (the DSN-less connections). This was written with 2007 in mind, so they didn't completely remove this from circulation in 2K7.
It tends to help quite a bit rather than trying to keep the file DSN's up to date on all machines. You should be able to use an IP address + port name or FQDN for the SQL server this way, and refresh it on a regular basis.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply