Who knows about VBE???

[Duritz]

'Cos I sure don't, and I need some help. I need to automate a process that will do calculations, then send the data to an Access database, then do more calculations, then send again, etc.

If someone can help, would they reply in this post, and I'll give them the details, ie the file locations etc etc, and they could write it for me? I am CLUELESS when it comes to VB, I want to learn, haven't had time yet.

I've tried some of those online excel/vb help forums, but they don't understand racing and therefore don't understand that this process needs to be done in Excel, and needs to be done HEAPS of times.

If anyone can help, it would be greatly appreciated. It wouldn't require a great deal of code. If no-one can, I understand too.

Thanks.

[bluetown]

Great Idea !

It's been done.
You can purchase for around $20,000.00


Or,
Qoute/
I am CLUELESS when it comes to VB, I want to learn, haven't had time yet.

Enrol in programm courses and start learning.

[Duritz]

Dude if you think that what I asked for would cost $20,000 then you've got your head up your you know what. I have written all the Excel stuff, just need someone to write the code that will do three things - calculate, refresh, send to Access. If that costs $20,000, then I know one thing, whatever you do for a living, I am glad I am not your client.

Actually, to further prove the point, a mate has introduced me to a programmer mate of his, he's doing it for me for a slab of beer. It'd want to be a good slab for 20 grand.

[bluetown]

Durizt,
I think you have misunderstood my answer to your question.

However
For a slab of beer, perhaps you can auction your system, let's say, a slab of beer and a packet of twisties ?

[Duritz]

Aah I see - yeah we have a miscommunication: I ain't selling (or buying) the method, I have already devised that, it's just a simple bit of programming I am after. You can have the Twisties all for yourself.

[bluetown]

Duritz,
What are the codes you cannot find answers for ?

[bluetown]

Here's a piece of code asked by someone I cannot find answers for, same reason, they (Excel Forum) do not seem to understand racing.

Sub Find_First()
Dim FindString As String
Dim Rng As Range
FindString = InputBox("Enter a Search value")

If Trim(FindString) <> "" Then
Set Rng = Range("A:A").find(What:=FindString, _
After:=Range("A" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End If
End Sub


I'll try and break it down.

Excel Sheet 1
Cell A1
Has a date always fixed in Sheet1 A1
But in Sheet 2 the same date may not be in Cell A1

They (the experts) say to type in the date where it says:

FindString = InputBox("Enter a Search value")

But that defeats the purpose because it's horse race and the dates keep changing.
The question, how is the code then written to find the fixed value of Sheet 1 A1 and find the same date or Value in Sheet 2 in whatever Cell it exists ?
Without having to type it in.

Is this the type of problems you get Duritz ?

[Duritz]

Nah that's not it but thanks for trying. What I am attempting to do is append large amounts of data from Excel to Access. Essentially, Excel will calculate stuff from a race meeting, send the results to Access, then move onto the next meeting, calculate it, then send the results, so on and so forth.

[bluetown]

You may try the other way round.
Append all your data to Access first, and take out what is required by Excel.
Then back to Access.
But I don't think Access is suited. There are other Databases around,but ofcourse that means starting from scratch.
BTW,
The above code was also just an example to.
There is a way to re-code a few lines and it will do the Find without having to type in the criteria. The point is though, you are correct when asking for code and such, but racing requirements I think is the least understood.

[marcus25]

You may try the other way round.
The point is though, you are correct when asking for code and such, but racing requirements I think is the least understood.

Hi Bluetown!
Sorry to butt in but this statement is just nonsense!
There is nothing in a database containing horse data that would make it unique.
As a matter of fact the fields are far easier to identify,and always the same type. Ie. just an example the name of Horse, venue etc is always a string, while the time ran is always single.
The approach taken by the poster by going to Excel then to Access then back again is very awkward to say the least.
Re. Acces not being the best, sorry it is the most suitable to use with Excel being from the same stable so to speak and sharing the same VBA language.
As to programming, again there is nothing in horseracing data that would make it specially hard to use.
Good luck with it.

[Duritz]

Hey thanks Marcus and Blue.

I am beginning to realise why it is akward to go from Excel to Access, however the calculations etc I need to do are HUGE, and I need to use Excel to do those calcs. Point is, I need to calculate answers for 40,000 race meetings, appending to Access the results after each one. Big task, I have set it all up, I just need the code to automate the sending of the answers to Access.

[bluetown]

You are welcome Duritz.
Try here:
http://www.cpearson.com/excel.htm

The point I made with the database, I am not sure what you mean Marcus.
Mine works fine. And correct though, Access and Excel have their limitations depending on requirements.

[marcus25]

You are welcome Duritz.
Try here:
http://www.cpearson.com/excel.htm

The point I made with the database, I am not sure what you mean Marcus.
Mine works fine. And correct though, Access and Excel have their limitations depending on requirements.
Hi blue!
What we have here is a failure to communicate!
I simply wanted to point out, that if someone was to use Excel as a front end to a database, (which I would not do in a fit) then Access would be more suitable than any other, having a common scripting language with Excel.
And also I find that people complaning about programmers not understanding the "special" requirements of horseracing is a furphy. There is nothing special about it, in the end it all comes down to data manipulation. Ie. you tell me how to treat beaten margins and I will allocate the number of points you think they deserve, and if you stipulate any other variable that would alter these points like class of race etc. that would be taken into account as well.
In the end, if there is a problem, I think it is the punters inability to convey to the programmer what they actually want the programme to do.
Re. Price. 20K is probably a bit over the top, although some poor souls have forked it out before and may I say, doing it now.
But by the same token, for a slab of beer, I would not even turn on my machine , unless it was for a friend, but for a friend I would do it for nix anyway.
Good luck

[Duritz]

Hi Marcus I'm Duritz, nice to meet you.

OK, now that we're friends, wanna write my code for me? :p

Seriously though, could you help me with something. I have found some code that does the job, but in trying to run it it says "syntax error" in one of the lines. If I paste the code here would you look at it and tell me why there's a syntax error, and how I fix it?

[KennyVictor]

And also I find that people complaning about programmers not understanding the "special" requirements of horseracing is a furphy. There is nothing special about it,

I seem to spend half my life programming racing stuff and there is one special requirement which costs me more time than anything else. The requirement is to match up the different versions of information which you get from different places. I get data from TABs, various results sites, etc and there is no uniform way to represent names, courses, jockeys, etc. One horse will be called Sheepshacker (NZ) by the TAB and Sheepshacker by a results site. It will be racing at Ellerslie or New Zealand or perhaps it will be at Broadmeadow or Newcastle. The Jockey can be Ms E Wright, E Wright or Eileen Wright or quite possibly the wrong jockey entirely. You don't realise how bad the standard of data is on the net until you try to match it all up.
Still, if it was easy anybody could do it couldn't they? :-)

KV

[Duritz]

KV that's no doubt true - but I get all my info from a DB provider, I pay for it of course but it means I get universality. (if that's a word).

Again, what I need is just to automate the appending of sections of data to an Access DB, and to tell it to repeat the process over and over.

[bluetown]

No,
It's all to clear what the problem is, and it is not communication entirely.

[marcus25]

I . It will be racing at Ellerslie or New Zealand or perhaps it will be at Broadmeadow or Newcastle. The Jockey can be Ms E Wright, E Wright or Eileen Wright or quite possibly the wrong jockey entirely. You don't realise how bad the standard of data is on the net until you try to match it all up.
Still, if it was easy anybody could do it couldn't they? :-)

KV
Hi KV!
Yes that could happen, although I use Cyberhorse data which is quite good as far as uniformity goes.
For track names, jockeys and trainers I introduced my own standard abbreviations, so the programme will automatically pick up the different spelling of the obviously same name! Getting data from the same source helps of course.
For horse names I tend to use tab number for identification instead, and always using racedate, racenumber, race venue as a unique handle mistakes are almost impossible to make.
Good luck with your projects! (as they say on 3AW on the morning show)

[KennyVictor]

Always interesting to see how the other half are working, I'm sure we all have similarities in the way we set things up. I use the same unique key you do Marcus, Date + course + race number - I guess it's obvious really.
Duritz, where do you buy your data? I 've considered the Racing Services Bureau as a source since I get my pre-race details from them - but I've noticed a few mistakes in their free data which has rather put me off. If Cyberhorse is 50c a race I shudder at the thought of the cost of, what, 30,000 races a year? Or is it cheaper in bulk?

Nice to swap programming thoughts.

KV

P.S. Sorry can't really help with your problem Duritz, wrong language for me.

[marcus25]

Cyberhorse is 50c a race I shudder at the thought of the cost of, what, 30,000 races a year? Or is it cheaper in bulk?

Nice to swap programming thoughts.
KV

Hi KV!
I pay around $250.0 a year, (dont take it for gospel, could be a bit more or less) and have access to every race download, (5 last starts) wheather it is a picnic or TAB meeting anywhere in OZ. The five last start is almost meaningless after a few month unless a new horse comes up that is not in your database yet.
This way you can build up a DB going back for years.
No connection to them, but I am happy with the service.

[Duritz]

Yeah it's cheaper in bulk KV.

There are a few DB service providers, Southcoast, Accuratings, RSB, VRC are the four I know best. I think TRB (website) do as well but slightly differently. The first 2 I mentioned cost about $4000 per year, I think RSB is more exe as is VRC (I think they're combined somehow).

The first two are well worth it, they also have an error rate in their data, but no data is totally clean. From them though you get all results, all fields and a ready made database in which to put it, or you can do like I do, use their results files to make your own DB. Sure quicker than getting it from websites.

[KennyVictor]

Thanks guys, I'll have a look at them all.

KV

[GungaDin]

Hey Duritz,

You still having problems with your transferring of data from Excel to Access? If so, let me know and I'll see what I can do.

Ideally, if you could send through a sample of the excel data (10 rows or so) in an excel spreadsheet and the structure of your access database (ideally in an empty database) then I should be able to lend a hand.

[bluetown]

Although I am no programmer in general.
I know this can work to according to the article.

http://www.aaronballman.com/programming/REALbasic/VbDeclToRbDecl.php

[bluetown]

Try
http://www.aaronballman.com/programming/REALbasic/VbDeclToRbDecl.php

You may have to email Arron and ask your specific, but it's another option and keeps choices "alive".

[kenchar]

Duritz,
I know bugger all about programming and don't want to but this might help you.
I got the site from the 12 year old son of a friend who wrote a programme or database or whatever you want to call it for his uncle.

www.REALbasic.com (http://www.realbasic.com/)

I hope this helps and maybe what you are looking for.

Cheers and good luck with your efforts.

[Duritz]

Thanks for all your help guys, greatly appreciated. Have been learning about VBE, and have sort of worked out how to do it. Basically, I write a command in an Excel macro to do the calcs, then another command which runs a macro I set up in Access to import the data, then back repeats over and over. Seems to be working, and though I am sure it's not the most efficient method, it'll be like the hare and the tortoise....

[bluetown]

No worries Duritz.

The method you have described, I have been there and done that, so to speak. Although I have not had to use Access, I found another database at the time was a bit easier to work with.

The point is this,Excel and Database (Any) is a very good way to use as proto-type, and it can remain as your "program" if it works for you, good !
From there though, I found there were requirements to enhance the system for it to do more. Meaning, everything Excel and a Database was already doing would then be replicated by developing a program.

I am sure when you get to the end of your current cycle of methods ( I know it can become tedious), the "logic" then will possibly direct you "program.exe" development.

If or when you do, and do some interim cost findings to have it developed, the costs are astounding in real dollar terms. This is ok to.
Programmers are like people, they need to eat,wash,pay the bills just the same as everyone else !
My quote was much more than the figure I posted when I got to this stage. ( No, I did not pay to have it done, incase anyone is wondering,but we done our homework prior.)

In hindsight, I would have intergrated some of the Database with the Database develoment package provided with RB. But RB was not up to it then, it is now with the latest version.
Once you do ever get to this stage and once you do have your "program.exe" (for lack of better terms), it's another "dimension".

Good luck with your projects.