I agree with Nathan on the technical side of things. For this type of use and data applications, while directly connecting to the database can or cannot be done with the “Get External Data” functions in Excel or Access, via ODBC or similar database connection technology in a data analysis package such as Stata or R, it might be preferable to use a database management tool such as Microsoft SQL Server Management Studio.
Indeed it is preferable and better to directly connect to the database through either a database management tool such as SQL Server Management Studio (Express), as compared to a data analysis package such as Excel, Access, Stata or R. If connecting through a data analysis package, the database is usually accessed by either setting up an ODBC Data Source (under Administrative tools under Control Panel) or on-the-fly via a connection string. For example, the ODBC connection string for the ARC database on our ARCTESTDATABASE server is:
Driver={SQL Server}; Server= ARCTESTDATABASE; Database=ARC; Integrated Security=SSPI
If using a .Net client to connect to the database, the connection string is slightly different:
Data Source= ARCTESTDATABASE; Initial Catalog=ARC; Integrated Security=SSPI
Once connected to the database, you can execute SQL statements against the database to perform custom analysis.
Here are some Database Back-end references we use here for this type of work:
· Microsoft SQL Server, http://www.microsoft.com/sqlserver
· SQL Server Management Studio Express, http://msdn.microsoft.com/en-us/library/ms365247.aspx free as Nathan stated
· T-SQL, http://msdn.microsoft.com/en-us/library/ms189826(v=sql.90).aspx
· WebORB, http://www.themidnightcoders.com
Thanks,
Guy
Guy Rousseau
Surveys & Transportation Model Development Manager
Atlanta Regional Commission
40 Courtland Street, NE
Atlanta, GA 30303
404-463-3274 (work)
678-986-4344 (cell)
CONFIDENTIALITY NOTICE: This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use or disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender immediately and destroy all copies of the original message.
From: ctpp-news-bounces@chrispy.net [mailto:ctpp-news-bounces@chrispy.net] On Behalf Of Erlbaum, Nathan (DOT)
Sent: Wednesday, March 09, 2011 12:29 PM
To: ctpp-news@chrispy.net
Subject: [CTPP] RE: Problem Importing CA PL data
From a database size issue on solution might be for you to try SQL server express
it may be a chore, but it is free and you can spend endless hours loving Microsoft. It will let you get the files in, since the size limit is MS Access is not the issue and then you can access them by MS access. It may be a steep learning curve for you but it is another option and it works because its free. Your other option is SAS or SPSS or maybe R or simply wait until someone else does it for you.
In any event what we have here a situation where the total lack of serious though leadership on the part of the Census Bureau would have resolved hundreds of hours of efforts on the part of many to do what the CB could have provided for in advance.
But then what can we expect, they’re from the government and they’re here to help you, I believe that is how the expression goes…….
From: ctpp-news-bounces@chrispy.net [mailto:ctpp-news-bounces@chrispy.net] On Behalf Of Anais Schenk
Sent: Wednesday, March 09, 2011 12:06 PM
To: ctpp-news@chrispy.net
Subject: [CTPP] RE: Problem Importing CA PL data
Thank you Robert.
It turns out the files are massive and that appears to be the problem. No matter which order I import in, Access can't import the third file. I've adopted your method and I've created two Access databases, one for the first PL data table and another for the second table. I was able to import the geo table into both and can query the data for our region's counties for use in GIS.
Thank you for saving me from a lot more frustration.
Anais Schenk | Planner | AMBAG
Direct 831.264.5088 | Fax 831.883.3755
From: ctpp-news-bounces@chrispy.net [mailto:ctpp-news-bounces@chrispy.net] On Behalf Of Allen, Robert.
Sent: Wednesday, March 09, 2011 7:00 AM
To: 'ctpp-news@chrispy.net'
Subject: [CTPP] RE: Problem Importing CA PL data
What size files are getting in Access when you try to import the files? In Texas, the files were too big to import all three at once into Access. Access has a code limited total data file size of 2 GB. I could import two of the files in any sequence and Access would always choke on the third, saying that the file was unrecognized or corrupt, but the actual problem was that the 2 GB limitation was being exceeded. Using a 64-bit version did not help as the 2 GB is a Microsoft Access program code limit, not a computing power limit. I finally had to hook the geoheaders up to the two data files in two steps, then cut down the resulting databases into substate areas that could be handled within the coding limitations of Access and Excel before hooking the data up to the census geography files in our GIS system.
Robert R. Allen, AICP
Abilene MPO
400 Oak St. #102, Abilene, TX 79602
Phone 325-676-6243
Fax 325-676-6398
From: ctpp-news-bounces@chrispy.net [mailto:ctpp-news-bounces@chrispy.net] On Behalf Of Anais Schenk
Sent: Tuesday, March 08, 2011 5:54 PM
To: ctpp-news@chrispy.net
Subject: [CTPP] Problem Importing CA PL data
Has anyone else had a problem importing the "cageo2010" file into Access 2003? I keep getting an error message saying that access doesn't recognize or find the database. I followed the instructions in the "0File_Structure" pdf exactly. I got the other two tables imported just fine, but can't seem to get the geo table.
Anais Schenk | Planner
Direct 831.264.5088 | Fax 831.883.3755
