February 26, 2009 at 12:18 pm
To start off, I am very new to SQL Server and have been using Access for many years but have limited coding experience. What I have learned/discovered I have done by trial and error and by googling and reading forums such as this. I now have an issue that I hope someone can shed light on for me. I have migrated my old Access database to utilize the Access front-end (2003) and a SQL Server 2008 back-end and have converted multiple queries to pass-through queries to utilize the server. Is there any way to have these queries reference a single entry for the odbc string rather than entering it into the properties of each query? I'm trying to eliminate maintenance time/issues if I have to update the string later. Thank you for any and all help.
Chris
February 27, 2009 at 7:42 am
Chris,
I am in your same boat! Have you figured out how to update the SQL server data from ACCESS?
Jim
March 2, 2009 at 7:35 am
If you create your front end system as an Access project (adp) rather than as an Access database (mdb) you have a single connection to the database and all queries are actually stored procedures and views in the SQL database. You also do not have linked tables, but connections to the SQL server tables.
March 2, 2009 at 10:21 am
Chris,
I have created an Access Project and it works great. My problem is I need to load massive amounts of production and cost data on a monthly basis. I tried SQL passthrough, which points to the SQL Server, but the database does not recognize the "current database" as the data source. I can use the import data feature, but it is blindly slow. There has to be a better way.
March 2, 2009 at 10:31 am
Why "load it" in access since SQL Server is the data server in this case.
You can base any reports, diagramsor whatever you need from a query on that data on sql server.
What are you trying to do exactly?
March 2, 2009 at 10:39 am
Chris,
I work for an oil and gas company, and I am the only one who knows anything about SQL and programming in general. Therefore, over the years, I have developed Access/VBA programs that contain an push-button GUI interface. The data I retrieve/load are large text files that are difficult to work with in any enviroment. I used SQL Server Express to import a file, and it was just as slow as Access. Thus far I am not all that impressed with SQL Server in general; I do not see the benifit switching from Access to SQL Server.
Jim
March 2, 2009 at 10:46 am
How big is the file and how long does it take you?
How many columns and how much data do you have to process?
March 2, 2009 at 10:50 am
Chris,
The file I have been playing with is approx. 198,000 lines and 20 columns. To add insult to injury, I am in Denver, CO, and I am trying to work with an SQL Server that is located in Tulsa, OK. The file takes 2 plus hours to load.
Jim
March 2, 2009 at 11:02 am
That should help :
zip
ftp to sql server
unzip
run import.
I have a crappy computer here and an import like that wouldn't take more than a few seconds.
Just for the fun of it. Can you zip the file and mail it to so that I can test the speed here?
March 2, 2009 at 11:10 am
Chris,
What is your email address?
Jim
March 2, 2009 at 11:44 am
Just did a test on my laptop, 4 M rows (3 columns wide) >> 50 Mb of data. data generation 4 seconds, export 6 seconds, import 5 seconds.
Lenovo 1.9 ghz dual core single proc, 3 gig ram, single 7200 rpm hd, vista sql 2005 dev.
So 2 hours is WAY too long for this... unless you have terabytes of data...
March 2, 2009 at 11:50 am
Chris,
It appears Access is the bottleneck. I wittled the file down to 28 rows, and it took Access at least 90 seconds to process the file. I then loaded the same file using SQL Server 2005, and it took 5 seconds.
Go figure!
Jim
March 2, 2009 at 11:54 am
should be 0 with only 28 rows. does that include the script generation?
March 2, 2009 at 12:02 pm
I assume that includes everything. Although I have a Computer Science degree, I have just started using SQL Server. I did quite a bit of mainframe SQL back in the 90's, but I have never had the opportunity to get into SQL Server. Therefore, like yourself, I consider myself to be fairly proficient with Access and databases in general. My problem is dumbing down the data so the average Excel user can access it.
Jim
March 2, 2009 at 12:06 pm
If you have office 2007, you could consider using sharepoint or analysis services so that they can manipulate the data themsleves (all in excel for the GUI). They would need 1-2 days of formation to learn how to use the analysis services in access and you'd need a couple days to play with it server side.
You wouldn't be an expert but you could get around to give them what they want assuming you have time to research it...
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply