An easier way to automatically get data into Access

ctorg · 10-06-2006, 11:24 AM

I've used Access a lot, but I have very rarely delved into any real VBA coding with it, so I went about setting up a way to automatically import all the csv files by making a very long macro that imports each one with a TransferText action. There are two glitches. One is that "NULL" in the players_streak.csv file creates error messages, which don't amount to much because it just erases "NULL". The other is that, in the leagues.csv file, all_star_text0 and 1 are messed up and need to have their data deleted in order for the file to import the rest of the lines (I don't even know what these are supposed to be).

I'm wondering if there is a more straightforward way of importing the data into Access. I know I can do an Access SQL dump, but I don't know how to get it into Access once the dump is done. I actually used the table creation lines from the Access SQL dump to create 61 separate action queries that create the tables and assign primary keys, since this was the only way I knew how to assign primary keys, and made a macro that would run all 61 action queries in a row.

This seems like a long workaround for something that should be pretty simple, though. I mean, it works, but is there an easier way?

Kyros · 10-11-2006, 10:26 PM

http://www.sim2005.com/Access/Default.htm

This is from Larry Anderson.

ctorg · 10-12-2006, 07:50 AM

Quote:

Originally Posted by Kyros

http://www.sim2005.com/Access/Default.htm

This is from Larry Anderson.

Thanks, but I already knew all that stuff. In fact, I basically made a set of macros and queries that automates the process of getting the data in. I was just wondering if there was anything simpler. The game allows a dump that creates a set of sql files that look like they would work in MS Access, but I don't know how to get them to run.

I guess if there was a way, Larry would have posted about it. I'm just baffled as to why there is a way to create these files if they are not usable.

Kyros · 10-12-2006, 01:59 PM

Quote:

Originally Posted by ctorg

Thanks, but I already knew all that stuff. In fact, I basically made a set of macros and queries that automates the process of getting the data in. I was just wondering if there was anything simpler. The game allows a dump that creates a set of sql files that look like they would work in MS Access, but I don't know how to get them to run.

I guess if there was a way, Larry would have posted about it. I'm just baffled as to why there is a way to create these files if they are not usable.

Sorry man. I wish I could have helped. If you do find a better way, please...let us know. I would love to know as well.

ctorg · 10-12-2006, 02:55 PM

I will. I've been searching all over for a way to do this stuff, but I can't find anything.

ctorg · 10-12-2006, 03:01 PM

In the meantime, I'll upload this MS Access file that contains what I do. Under Macros, if you run CreateAllTables, it will set up all the tables. Then run ImportAll to import the data. Two catches are that you have to make the corrections I mentioned above and the csv files have to be in c:\OOTPdata on your hard drive (so move them there first).

Also, it helps if you turn off warning messages for action queries so that you don't get a bunch of them, since the macro runs a series of queries.

Kyros · 10-12-2006, 03:25 PM

Quote:

Originally Posted by ctorg

In the meantime, I'll upload this MS Access file that contains what I do. Under Macros, if you run CreateAllTables, it will set up all the tables. Then run ImportAll to import the data. Two catches are that you have to make the corrections I mentioned above and the csv files have to be in c:\OOTPdata on your hard drive (so move them there first).

Also, it helps if you turn off warning messages for action queries so that you don't get a bunch of them, since the macro runs a series of queries.

Very....very cool! Thanks!!!

toddnorr · 11-21-2006, 01:02 PM

I am not completely sure I understand what your question is. What I think you are saying is that once you do the SQL dump you are having to create an extensive macro to get the data into Access and run that macro each time you dump.

If that is indeed what you are doing then it is similar to what I was doing. I found thought that it was easier to export to a CSV file and link the tables I wanted in Access to the files in the import_export folder. Doing it this way I do not have to run code each time I dump to update the Access tables.

Hope that made some sense. If not let me know and I will try again.

Good Luck!

ctorg · 11-21-2006, 02:16 PM

Quote:

Originally Posted by toddnorr

I am not completely sure I understand what your question is. What I think you are saying is that once you do the SQL dump you are having to create an extensive macro to get the data into Access and run that macro each time you dump.

If that is indeed what you are doing then it is similar to what I was doing. I found thought that it was easier to export to a CSV file and link the tables I wanted in Access to the files in the import_export folder. Doing it this way I do not have to run code each time I dump to update the Access tables.

Hope that made some sense. If not let me know and I will try again.

Good Luck!

I suppose linking it would make things a bit easier, but right now, I am trying to get all the years into a single database, which has become a bit of a complicated project. Basically, I have to add year fields to any place where there are no years and such. In this case, I need to have the data in unalterable tables rather than linked. I actually haven't worked on this in several weeks, so I've gotten a little fuzzy on exactly what I was doing now.

Comedian2004 · 11-21-2006, 03:17 PM

You may want to check out my AUContract program. It basically loads a whole bunch of SQL files into memory and then creates one large CSV dump that you can import into Access.

ctorg · 11-22-2006, 08:16 AM

Quote:

Originally Posted by Comedian2004

You may want to check out my AUContract program. It basically loads a whole bunch of SQL files into memory and then creates one large CSV dump that you can import into Access.

Thanks. That sounds useful. Due to time constraints caused by having two preschool kids running around, I haven't had the time to check out your utilities much yet (or even work on my own league ideas much). I keep hearing they're great, so I'll have to see if I can make time.

10-06-2006, 11:24 AM	#1
ctorg Global Moderator Join Date: Nov 2002 Location: Queens, NY Posts: 9,848	An easier way to automatically get data into Access I've used Access a lot, but I have very rarely delved into any real VBA coding with it, so I went about setting up a way to automatically import all the csv files by making a very long macro that imports each one with a TransferText action. There are two glitches. One is that "NULL" in the players_streak.csv file creates error messages, which don't amount to much because it just erases "NULL". The other is that, in the leagues.csv file, all_star_text0 and 1 are messed up and need to have their data deleted in order for the file to import the rest of the lines (I don't even know what these are supposed to be). I'm wondering if there is a more straightforward way of importing the data into Access. I know I can do an Access SQL dump, but I don't know how to get it into Access once the dump is done. I actually used the table creation lines from the Access SQL dump to create 61 separate action queries that create the tables and assign primary keys, since this was the only way I knew how to assign primary keys, and made a macro that would run all 61 action queries in a row. This seems like a long workaround for something that should be pretty simple, though. I mean, it works, but is there an easier way? __________________ My music "When the trees blow back and forth, that's what makes the wind." - Steven Wright Fjord emena pancreas thorax fornicate marmalade morpheme proteolysis smaxa cabana offal srue vitriol grope hallelujah lentils

10-12-2006, 02:55 PM	#5
ctorg Global Moderator Join Date: Nov 2002 Location: Queens, NY Posts: 9,848	I will. I've been searching all over for a way to do this stuff, but I can't find anything. __________________ My music "When the trees blow back and forth, that's what makes the wind." - Steven Wright Fjord emena pancreas thorax fornicate marmalade morpheme proteolysis smaxa cabana offal srue vitriol grope hallelujah lentils

11-21-2006, 03:17 PM	#10
Comedian2004 Hall Of Famer Join Date: Nov 2004 Location: In a house in Saint Cloud, Florida. Posts: 7,085	You may want to check out my AUContract program. It basically loads a whole bunch of SQL files into memory and then creates one large CSV dump that you can import into Access. __________________ Like BLUES? Visit www.smokestacklightnin.com, you will LOVE it! New show every Monday!! New Blues HOF![/COLOR][/FONT]

10-11-2006, 10:26 PM	#2
Kyros All Star Reserve Join Date: May 2006 Posts: 976	http://www.sim2005.com/Access/Default.htm This is from Larry Anderson.

11-21-2006, 01:02 PM	#8
toddnorr Minors (Single A) Join Date: Nov 2003 Posts: 78	I am not completely sure I understand what your question is. What I think you are saying is that once you do the SQL dump you are having to create an extensive macro to get the data into Access and run that macro each time you dump. If that is indeed what you are doing then it is similar to what I was doing. I found thought that it was easier to export to a CSV file and link the tables I wanted in Access to the files in the import_export folder. Doing it this way I do not have to run code each time I dump to update the Access tables. Hope that made some sense. If not let me know and I will try again. Good Luck!