If I had a field called "Name" in an Access table "Contact Info", and the
field contained VBScript...
Function Main(rstFields)
Main = rstFields.Item("FirstName").Value
End Function
1. How do I pass a recordset into this field?
2. How do I capture the return value?
Thanks,
Adam
46  3670 
Adam Turner via AccessMonster.com wrote: If I had a field called "Name"
Bad idea, "Name" is a reserved word.
in an Access table "Contact Info",
again, spaces in table names will trip you up later on.
and the
field contained VBScript...
There is no VBScript in tables, this goes in modules either on their own
or code behind a form.
Function Main(rstFields)
Main = rstFields.Item("FirstName").Value
End Function
1. How do I pass a recordset into this field?
2. How do I capture the return value?
Given the above, I've no idea where you're going with this.
I suggest you get a beginner's book on Access and learn the fundimentals
before posting here. This isn't the place to learn what a table is, etc.
Trevor Best wrote: If I had a field called "Name"
Bad idea, "Name" is a reserved word.
in an Access table "Contact Info",
again, spaces in table names will trip you up later on.
and the
field contained VBScript...
There is no VBScript in tables, this goes in modules either on their own
or code behind a form.
Function Main(rstFields)
Main = rstFields.Item("FirstName").Value
End Function
1. How do I pass a recordset into this field?
2. How do I capture the return value?
Given the above, I've no idea where you're going with this.
I suggest you get a beginner's book on Access and learn the fundimentals
before posting here. This isn't the place to learn what a table is, etc.
Thanks for etiquette advice but I was looking for more programming advice.
Is anyone familiar with VBScript in Access Tables?
Please do not reply if you believe this isn't possible.
Thanks,
Adam
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
"Adam Turner via AccessMonster.com" <u14766@uwe> wrote in message
news:55b13d882d2da@uwe... Trevor Best wrote: If I had a field called "Name"
Bad idea, "Name" is a reserved word.
in an Access table "Contact Info",
again, spaces in table names will trip you up later on.
and the
field contained VBScript...
There is no VBScript in tables, this goes in modules either on their own
or code behind a form.
Function Main(rstFields)
Main = rstFields.Item("FirstName").Value
End Function
1. How do I pass a recordset into this field?
2. How do I capture the return value?
Given the above, I've no idea where you're going with this.
I suggest you get a beginner's book on Access and learn the fundimentals
before posting here. This isn't the place to learn what a table is, etc.
Thanks for etiquette advice but I was looking for more programming advice.
Is anyone familiar with VBScript in Access Tables?
Please do not reply if you believe this isn't possible.
Thanks,
Adam
You don't need to do much Googling to find out that Trevor is both
knowledgeable and helpful and has probably spent many years writing
vb/vbscript/vba. I am sure he would be genuinely happy to help out - that
goes for me too. However, I would second Trevor's opinion that I can't see
where you're going with that function, or quite what the question is. Why
not try re-phrasing it and let us know whether you are pretty much new to
all of this. Certainly the bit "...and the field contained VBScript..."
does not make much sense to me.
Brian Wilson wrote: If I had a field called "Name"
[quoted text clipped - 31 lines]
Adam
You don't need to do much Googling to find out that Trevor is both
knowledgeable and helpful and has probably spent many years writing
vb/vbscript/vba. I am sure he would be genuinely happy to help out - that
goes for me too. However, I would second Trevor's opinion that I can't see
where you're going with that function, or quite what the question is. Why
not try re-phrasing it and let us know whether you are pretty much new to
all of this. Certainly the bit "...and the field contained VBScript..."
does not make much sense to me.
If the following VB Script (or any script) were in an Access table, my
question was...how do I pass a recordset into it?(considering it's only text)
Function Main(rstFields)
Main = True
Main = rstFields.Item("ReceiptDate").Value > rstFields.Item("EndDate")
End Function
I'm developing a batch processor that validates submissions from consumers.
I'm populating fields in Access with VBScript that validates each feild on
the form they submit. In the above example, if Main = True, then the next
field in the record will run another Main() function. If at any time the VB
Script returns false, it simply populates another field in another table and
terminates.
I can't elaborate any further without drawing out the infrastructure.
Sorry for any confusion I might have caused...but I did find my answer.
Thanks,
Adam
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
"Adam Turner via AccessMonster.com" <u14766@uwe> wrote Is anyone familiar with VBScript in
Access Tables?
Please do not reply if you believe
this isn't possible.
Why don't you explain what you mean by "VBScript in Access Tables"? I can
assure you that Trevor Best knows whereof he speaks when it comes to
Microsoft Access, and what is officially called "vbscript" by Microsoft --
perhaps we are having some difficulty understanding what you are asking.
VB script, if we are talking about the same thing, is simply text that is
interpreted by the Windows Scripting Host, and text can, of course, be
stored in Access tables. But I get the feeling that you are talking about
something more than that.
Indeed, there is little more that you can do with vbscript in Access tables
than store and retrieve it.
Larry Linson
Microsoft Access MVP
"Adam Turner via AccessMonster.com" <u14766@uwe> wrote in message
news:55b2f374118b6@uwe... Trevor Best wrote:There is no VBScript in tables, this goes in modules either on their own
or code behind a form.
Just as an FYI...you can put any script in an Access table and it's an
excellent method to validate recordsets.
If anyone is interested, I found my answer here:
http://www.vb-helper.com/howto_evalu...t_control.html
I'd say your choice of wording made it a little difficult to follow what you
want to do... the article you cite does not appear to me to either store
vbscript in nor use vbscript from a field in an Access table, and it is in
VB, not in Access VBA, just FYI.
As far as I can see, you don't "pass a recordset in to" anything. You create
code on the fly... not something I have very often found needful. Of course,
the text used to be run with the "eval" function of the Script Control could
have come from an Access table field. Access, BTW, has its own "eval"
function, so you could do something similar with VBA in Access, using the
native eval function and not use either the script control nor vbscript.
vbscript and VBA are different, as I am sure you know.
You'll find, by careful Googling, occasional posts about using vbscript to
provide user "macros" or "user scripting" in developed Access applications
where the user isn't given design privileges. It's done more often in
classic VB, however, as in the example you cite.
Larry Linson
Microsoft Access MVP
Eeeeeeeeeeeewwwwwwwwwwwwwwwwwwwwwwwww!
Adam Turner via AccessMonster.com wrote:
Thanks for etiquette advice but I was looking for more programming advice.
Is anyone familiar with VBScript in Access Tables?
Please do not reply if you believe this isn't possible.
That would be bad. It's like if you posted to a car forum and said you
attached the steering wheel to the roof and the seats are in the engine
bay now want to know how to drive it. You're so far down the wrong road
(no pun intended) if anyone came up with advice on how to drive that
wierd looking car apart from dismantling it and putting it back together
properly then the advice would be really poor and get you into futher
trouble.
"Adam Turner via AccessMonster.com" <u14766@uwe> wrote in message
news:55b30ac15818a@uwe... Brian Wilson wrote:> If I had a field called "Name"
[quoted text clipped - 31 lines]
Adam
You don't need to do much Googling to find out that Trevor is both
knowledgeable and helpful and has probably spent many years writing
vb/vbscript/vba. I am sure he would be genuinely happy to help out - that
goes for me too. However, I would second Trevor's opinion that I can't
see
where you're going with that function, or quite what the question is. Why
not try re-phrasing it and let us know whether you are pretty much new to
all of this. Certainly the bit "...and the field contained VBScript..."
does not make much sense to me.
If the following VB Script (or any script) were in an Access table, my
question was...how do I pass a recordset into it?(considering it's only
text)
Function Main(rstFields)
Main = True
Main = rstFields.Item("ReceiptDate").Value > rstFields.Item("EndDate")
End Function
I'm developing a batch processor that validates submissions from
consumers.
I'm populating fields in Access with VBScript that validates each feild on
the form they submit. In the above example, if Main = True, then the next
field in the record will run another Main() function. If at any time the
VB
Script returns false, it simply populates another field in another table
and
terminates.
I can't elaborate any further without drawing out the infrastructure.
Sorry for any confusion I might have caused...but I did find my answer.
Thanks,
Adam
Well, I'm glad you are happy with the answer you found - it doesn't seem to
make much sense to any of the people here - but it's not our application.
What is not clear is how the users are adding the records - this could be
done using a number of methods. Often, this is done using an Access
application which uses bound forms - but it could also be done, say, via an
asp web page. Then you are at some point validating the data, but it is not
clear whether you are doing this as they enter the records, or after they
have added a whole batch.
"Normally" you have the form bound to the table and you would write code
(VBA) to validate the records as they were added - so it would be impossible
to add invalid records. If, however, you have a table of records which have
already been added and may contain both valid and invalid records, then you
would probably write a routine which might take the original table and split
it into two tables: valid records and invalid records with an extra column
describing why the record is invalid.
"Larry Linson" <bo*****@localhost.not> wrote in
news:U2_2f.39905$HM1.10701@trnddc04: vbscript and VBA are different, as I am sure you know.
I think he *doesn't* know this, and that's why there's a problem.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
"Adam Turner via AccessMonster.com" <u14766@uwe> wrote in
news:55b30ac15818a@uwe: Brian Wilson wrote:> If I had a field called "Name"
[quoted text clipped - 31 lines]
Adam
You don't need to do much Googling to find out that Trevor is both
knowledgeable and helpful and has probably spent many years
writing vb/vbscript/vba. I am sure he would be genuinely happy to
help out - that goes for me too. However, I would second Trevor's
opinion that I can't see where you're going with that function, or
quite what the question is. Why not try re-phrasing it and let us
know whether you are pretty much new to all of this. Certainly
the bit "...and the field contained VBScript..." does not make
much sense to me.
If the following VB Script (or any script) were in an Access
table, my question was...how do I pass a recordset into
it?(considering it's only text)
Function Main(rstFields)
Main = True
Main = rstFields.Item("ReceiptDate").Value >
rstFields.Item("EndDate")
End Function
I'm developing a batch processor that validates submissions from
consumers.
I'm populating fields in Access with VBScript that validates each
feild on the form they submit. In the above example, if Main =
True, then the next field in the record will run another Main()
function. If at any time the VB Script returns false, it simply
populates another field in another table and terminates.
You have code snippets stored in tables that you want to run.
That wasn't at all clear from your first post.
Second, the environment in which you want to run this is highly
obscure because you say "VBScript", which has zilch to do with
Access, which uses VBA as its scripting language. VBA and VBScript
have a lot in common, but they ain't at all the same thing.
Your approach would make a lot of sense in a web browser-based
application that was driven by a server-side scripting language,
where you could create the custom script from the code stored in the
tables.
However, it seems to me that your example would be much better
handled by general-purpose code that would have certain evaluation
rules. Either way, you're going to have a nasty CASE SELECT that
determines what to do based on the fieldname, or you're going to
have a huge amount of code duplication, with structurally the same
code appearing in a bunch of fields.
Of course, all of the above is assuming that I've understood what
you're trying to do.
My first thought upon reading your initial post was Eval(), but my
experience tells me that circumstnaces that require resorting to
Eval() are usually the result of massive flaws in the underlying
design. Since I had no clue about the context or meaning of your
question, I chose not to answer.
I'm glad you've found an answer that satisfies you.
It remains to be seen whether or not the answer will work or not.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Brian Wilson wrote: >> If I had a field called "Name"
>
[quoted text clipped - 39 lines]
Adam
Well, I'm glad you are happy with the answer you found - it doesn't seem to
make much sense to any of the people here - but it's not our application.
What is not clear is how the users are adding the records - this could be
done using a number of methods. Often, this is done using an Access
application which uses bound forms - but it could also be done, say, via an
asp web page. Then you are at some point validating the data, but it is not
clear whether you are doing this as they enter the records, or after they
have added a whole batch.
"Normally" you have the form bound to the table and you would write code
(VBA) to validate the records as they were added - so it would be impossible
to add invalid records. If, however, you have a table of records which have
already been added and may contain both valid and invalid records, then you
would probably write a routine which might take the original table and split
it into two tables: valid records and invalid records with an extra column
describing why the record is invalid.
Close Brian,
Data Entry keys a batch and releases them to a batch processor. The batch
processor passes each record through each "Function Main(rstFields)" (and
there are many Main(rstFields)) which are stored in .mdb tables. Depending on
what Main() returns, it continues validating or stops.
I'm writing a stand alone app that does bogus test batching. I need to mirror
the batch processor. The batch processor uses .asp script controls to do this.
My application will have to use the primitive VB6 Script controller which I
didn't know existed until I found the link I posted.
Thanks,
Adam
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
David W. Fenton wrote: >> If I had a field called "Name"
>
[quoted text clipped - 30 lines] function. If at any time the VB Script returns false, it simply
populates another field in another table and terminates.
You have code snippets stored in tables that you want to run.
That wasn't at all clear from your first post.
Second, the environment in which you want to run this is highly
obscure because you say "VBScript", which has zilch to do with
Access, which uses VBA as its scripting language. VBA and VBScript
have a lot in common, but they ain't at all the same thing.
Your approach would make a lot of sense in a web browser-based
application that was driven by a server-side scripting language,
where you could create the custom script from the code stored in the
tables.
However, it seems to me that your example would be much better
handled by general-purpose code that would have certain evaluation
rules. Either way, you're going to have a nasty CASE SELECT that
determines what to do based on the fieldname, or you're going to
have a huge amount of code duplication, with structurally the same
code appearing in a bunch of fields.
Of course, all of the above is assuming that I've understood what
you're trying to do.
My first thought upon reading your initial post was Eval(), but my
experience tells me that circumstnaces that require resorting to
Eval() are usually the result of massive flaws in the underlying
design. Since I had no clue about the context or meaning of your
question, I chose not to answer.
I'm glad you've found an answer that satisfies you.
It remains to be seen whether or not the answer will work or not.
Thanks for the reply David, however I wish it would've been sooner. Eval()
was exactly what I was looking for. Hopefully as I reply to others' responses,
my intent will become clearer.
Adam
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
Adam Turner via AccessMonster.com wrote: Trevor Best wrote:
There is no VBScript in tables, this goes in modules either on their own
or code behind a form.
Just as an FYI...you can put any script in an Access table and it's an
excellent method to validate recordsets.
If anyone is interested, I found my answer here:
http://www.vb-helper.com/howto_evalu...t_control.html
I thought you were talking something along the lines of a trigger, but
yes you can *store* anything in a table, including script but executing
it is another matter, Eval() evaluates an expression, which can contain
a function call but that function must be in a callable module within
your code. I don't think you could execute entire scripts this way.
I fail to see what you mean by "validate recordsets" though.
Trevor Best wrote: Thanks for etiquette advice but I was looking for more programming advice.
Is anyone familiar with VBScript in Access Tables?
Please do not reply if you believe this isn't possible.
That would be bad. It's like if you posted to a car forum and said you
attached the steering wheel to the roof and the seats are in the engine
bay now want to know how to drive it. You're so far down the wrong road
(no pun intended) if anyone came up with advice on how to drive that
wierd looking car apart from dismantling it and putting it back together
properly then the advice would be really poor and get you into futher
trouble.
Huh? I'm assuming that you're suggesting Access is only used as common
storage. Anything outside of that is unorthodox or taboo...Is this right?
Relational databases are used in a number of fashions. To scold or heckle
someone for a question they asked that is slightly outside the Playtel Access
paradigm that "you're" used to is honestly immature.
....but pay attention to the thread Trevor. You might learn something
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
"Adam Turner via AccessMonster.com" <u14766@uwe> wrote in
news:55c01e603410b@uwe: David W. Fenton wrote:
[] I'm glad you've found an answer that satisfies you.
It remains to be seen whether or not the answer will work or not.
Thanks for the reply David, however I wish it would've been
sooner. Eval() was exactly what I was looking for. Hopefully as I
reply to others' responses, my intent will become clearer.
Well, my whole point in making a lengthy reply was that I didn't
understand that Eval() was what you were looking for until you
clarified extensively what your question was.
That's exactly what Trevor asked you for, clarification, and you
took him to task for asking for the clarification.
Your original post was impossible to answer, since it just wasn't
clear what you were asking, and that's basically why I didn't think
it was worth replying until there'd been clarification.
I almost didn't post at all after you were rude to Trevor for asking
for more information. In the future, consider all of that when
posting to the newsgroup.
No one here owes you any help at all, and it's up to you to post in
a fashion that maximizes the possibility of somebody being nice and
giving you an answer to your questions.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
"Adam Turner via AccessMonster.com" <u14766@uwe> wrote in
news:55c03292d0cb3@uwe: Relational databases are used in a number of fashions. To scold or
heckle someone for a question they asked that is slightly outside
the Playtel Access paradigm that "you're" used to is honestly
immature.
Keep this up and you'll end up in my killfile (and probably that of
a large number of other regular readers/posters in CDMA). Trevor is
anything but an amateur with Access -- he's one of the most advanced
Access programmers in this newsgroup, and does a lot of good work
answering questions.
The problem here is not *Trevor* -- it's your seeming inability to
define your question in a way that people who develop in Access
professionally (which includes me and Trevor) can understand what
you're asking.
You look awfully rude to me, blaming others for the inadequacy of
your own post.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
David W. Fenton wrote: Relational databases are used in a number of fashions. To scold or
heckle someone for a question they asked that is slightly outside
the Playtel Access paradigm that "you're" used to is honestly
immature.
Keep this up and you'll end up in my killfile (and probably that of
a large number of other regular readers/posters in CDMA). Trevor is
anything but an amateur with Access -- he's one of the most advanced
Access programmers in this newsgroup, and does a lot of good work
answering questions.
The problem here is not *Trevor* -- it's your seeming inability to
define your question in a way that people who develop in Access
professionally (which includes me and Trevor) can understand what
you're asking.
You look awfully rude to me, blaming others for the inadequacy of
your own post.
Your "kill" file? lol too funny...I needed that...thanks
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
"Adam Turner via AccessMonster.com" <u14766@uwe> wrote in
news:55c2d27c2fb4a@uwe: David W. Fenton wrote: Relational databases are used in a number of fashions. To scold
or heckle someone for a question they asked that is slightly
outside the Playtel Access paradigm that "you're" used to is
honestly immature.
Keep this up and you'll end up in my killfile (and probably that
of a large number of other regular readers/posters in CDMA).
Trevor is anything but an amateur with Access -- he's one of the
most advanced Access programmers in this newsgroup, and does a lot
of good work answering questions.
The problem here is not *Trevor* -- it's your seeming inability to
define your question in a way that people who develop in Access
professionally (which includes me and Trevor) can understand what
you're asking.
You look awfully rude to me, blaming others for the inadequacy of
your own post.
Your "kill" file? lol too funny...I needed that...thanks
OK, you asked for it -- you won't be getting help from me ever
again.
<PLONK>
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
David W. Fenton wrote: Relational databases are used in a number of fashions. To scold
or heckle someone for a question they asked that is slightly
[quoted text clipped - 16 lines]
Your "kill" file? lol too funny...I needed that...thanks
OK, you asked for it -- you won't be getting help from me ever
again.
<PLONK>
lol...ok...but I think you have to help someone first before you can help
them ever again...dipshit
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
You used a site which supports itself by sucking the expertise from
groups such as ours, and their regulars.
You posted a question which was not clear.
You were rude to those who questionned your intent.
You posted a trivial and simplistic solution of mediocre code.
You failed to defend the assumption implicit in your posts, that is
that storing script in fields (and interpreting it and executing it
later) has merit (I doubt that this is so, but if you were capable and
willing to describe clearly your purpose in doing so I might be able to
arrive at a more reasoned opinion; rgardless I expect that some regular
here will have an simpler, better solution to what you are trying to
achieve).
And now you resort to name calling.
Go away.
lylefair wrote: You used a site which supports itself by sucking the expertise from
groups such as ours, and their regulars.
You posted a question which was not clear.
You were rude to those who questionned your intent.
You posted a trivial and simplistic solution of mediocre code.
You failed to defend the assumption implicit in your posts, that is
that storing script in fields (and interpreting it and executing it
later) has merit (I doubt that this is so, but if you were capable and
willing to describe clearly your purpose in doing so I might be able to
arrive at a more reasoned opinion; rgardless I expect that some regular
here will have an simpler, better solution to what you are trying to
achieve).
And now you resort to name calling.
Go away.
Ok. Enlighten me. How would you ask the question?
--
Message posted via http://www.accessmonster.com
If I understood the question I would know better how I might ask it.
Perhaps:
In a field in a table is this string:
Function Main(rstFields)
Main = rstFields.Item("FirstName").Value
End Function
How can I "run" the function and obtain the value it returns?
How can I pass the parameter rstFields to the function?
lylefair wrote: If I understood the question I would know better how I might ask it.
Perhaps:
In a field in a table is this string:
Function Main(rstFields)
Main = rstFields.Item("FirstName").Value
End Function
How can I "run" the function and obtain the value it returns?
How can I pass the parameter rstFields to the function?
Yes you're right. I was way off base with my approach. Our questions weren't
even close.
First...it's assumed I have to "execute" the function before I can pass
anything to it or capture the return value. (All the information you needed
to answer the question was there if you knew anything about programming)
Secondly, Access & VB6 or VBScript, or VBA (rofl) are all toys. Anyone who
considers any of them to be of any caliber of difficulty should change
careers.
It has been my experience that these forums are flooded with wanna be
amateurs with little knowledge of real programming that waste everyone's time
by disguising programming skill with critique and heckling.
If anyone of you Pro-Trevor ball lickers can show where Trevor, or anyone
else for that matter, has not posted something scornful and inappropriate, I
will apologize. Until then, keep practicing.
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
Adam Turner via AccessMonster.com wrote: It has been my experience that these forums are flooded with wanna be
amateurs with little knowledge of real programming that waste everyone's time
by disguising programming skill with critique and heckling.
Why did you post your question here then?
If anyone of you Pro-Trevor ball lickers can show where Trevor, or anyone
else for that matter, has not posted something scornful and inappropriate, I
will apologize. Until then, keep practicing.
This is name calling (again) and rude.
Perhaps, you could post some of your best coding/programming examples
so that we can be respectful of your skills? Most of us are familiar
with more then one programming technology, so it's likely that at least
a few of us will be able to understand and appreciate your work. Google
Web Searches for "adam turner" program and "adam turner" code did not
reveal these to me.
"lylefair" <ly***********@aim.com> wrote in
news:11********************@z14g2000cwz.googlegrou ps.com: Google
Web Searches for "adam turner" program and "adam turner" code did
not reveal these to me.
Perhaps you misspelled "Don Mellon."
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
lylefair wrote: It has been my experience that these forums are flooded with wanna be
amateurs with little knowledge of real programming that waste everyone's time
by disguising programming skill with critique and heckling.
Why did you post your question here then?
If anyone of you Pro-Trevor ball lickers can show where Trevor, or anyone
else for that matter, has not posted something scornful and inappropriate, I
will apologize. Until then, keep practicing.
This is name calling (again) and rude.
Perhaps, you could post some of your best coding/programming examples
so that we can be respectful of your skills? Most of us are familiar
with more then one programming technology, so it's likely that at least
a few of us will be able to understand and appreciate your work. Google
Web Searches for "adam turner" program and "adam turner" code did not
reveal these to me.
....because sometimes, but not often, these newsgroups can expose a diamond in
the rough. Although the accurance is rare, the benefits are, more often than
not, worth the effort.
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
You forgot to answer this part:
"Perhaps, you could post some of your best coding/programming examples
so that we can be respectful of your skills? Most of us are familiar
with more then one programming technology, so it's likely that at least
a few of us will be able to understand and appreciate your work. Google
Web Searches for "adam turner" program and "adam turner" code did not
reveal these to me. "
lylefair wrote: You forgot to answer this part:
"Perhaps, you could post some of your best coding/programming examples
so that we can be respectful of your skills? Most of us are familiar
with more then one programming technology, so it's likely that at least
a few of us will be able to understand and appreciate your work. Google
Web Searches for "adam turner" program and "adam turner" code did not
reveal these to me. "
Sure...here's a current project that should be up everyone's alley (it's
somewhat lengthy):
Private Sub cmdDatabase_Click()
'Reference the following
'*Microsoft ADO Ext. 2.1 for DDL and Security
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\aturner\Desktop\Batch Tester5\
BogusTestBatch.mdb;"
End Sub
Private Sub cmdCreateTables_Click()
Dim MyDatabase As Database
Dim Submission As TableDef
Dim SubmitClient000015 As TableDef
Dim ProductClient000015 As TableDef
Dim sDataType As String
Dim cn As ADODB.Connection
Dim cmd As Command
Dim rstFields As ADODB.Recordset
Dim sFieldName, sType As String
Dim iLength, iOfferTableID, iDisplayed As Integer
Dim iSubmission, iSpecial, iProduct As Integer
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
With cn
.ConnectionString = "Provider=SQLOLEDB;Integrated Security=SSPI;Data
Source=gfssvr15;Initial Catalog=GFS;"
.CursorLocation = adUseClient
.Mode = adModeRead
.Open
End With
With cmd
.CommandText = "SELECT o.FieldName, o.Type, o.Length, o.OfferTableID, o.
Displayed, oc.ClientID " & _
"FROM DBO.OfferDictionary o(NOLOCK) " & _
"JOIN DBO.OfferClient oc(NOLOCK) ON o.OfferID = oc.
OfferID " & _
"WHERE o.OfferID = 22624 " & _
"ORDER BY o.OfferTableID"
.CommandType = adCmdText
.ActiveConnection = cn
End With
Set rstFields = cmd.Execute
iSubmission = rstFields.Fields(3).Value
iSpecial = iSubmission + 1
'iProduct = iSubmission + 2
'open database
Set MyDatabase = OpenDatabase(App.Path + "\BogusTestBatch.mdb")
Call fGetClientTableName(rstFields.Fields(5).Value) 'Pass clientid to
check for client level tables and return table name
'create the table
Set Submission = MyDatabase.CreateTableDef("Submission")
Set SubmitClient000015 = MyDatabase.CreateTableDef("SubmitClient000015")
Set ProductClient000015 = MyDatabase.CreateTableDef("ProductClient000015")
On Error Resume Next
'delete the table if it already exists
MyDatabase.TableDefs.Delete Submission.Name
MyDatabase.TableDefs.Delete SubmitClient000015.Name
MyDatabase.TableDefs.Delete ProductClient000015.Name
Do While Not rstFields.EOF
sFieldName = rstFields.Fields(0).Value 'Fields(0).Value =
Fieldname
sType = rstFields.Fields(1).Value 'Fields(1).Value = Type
iLength = rstFields.Fields(2).Value 'Fields(2).Value = Length
iOfferTableID = rstFields.Fields(3).Value 'Fields(3).Value =
OfferTableID
iDisplayed = rstFields.Fields(4).Value 'Fields(4).Value =
Displayed
Select Case iOfferTableID 'add fields to the tables
Case iSubmission 'ORDER BY OfferTableID in SELECT statment is the
key
With Submission
If sFieldName = "RecordID" Then
.Fields.Append .CreateField("RecordID", PrimaryKey,
10)
Else
.Fields.Append .CreateField(sFieldName,
fConvertDatatypes(sType), iLength)
End If
End With
'pAddRecords (iDisplayed)
Case iSpecial
With SubmitClient000015
.Fields.Append .CreateField(sFieldName, fConvertDatatypes
(sType), iLength)
End With
'pAddRecords (iDisplayed)
Case Else 'there's not always a product table
With ProductClient000015
.Fields.Append .CreateField(sFieldName, fConvertDatatypes
(sType), iLength)
End With
'pAddRecords (iDisplayed)
End Select
'NewTable.Indexes.Refresh
MyDatabase.TableDefs.Append Submission
MyDatabase.TableDefs.Append SubmitClient000015
MyDatabase.TableDefs.Append ProductClient000015
rstFields.MoveNext
Loop
'Debug.Print rstFields.RecordCount
MyDatabase.Close 'close database
Set MyDatabase = Nothing
cn.Close 'close connection
Set cn = Nothing
'Call MsgBox("Tables Created Successfully", vbOKOnly)
End Sub
Private Sub pAddRecords(iDisplayed As Boolean)
'Displayed needs populated
'must get retailer from offerdictionary
'get sku or model if available
End Sub
Private Function fConvertDatatypes(sDataType As String)
Select Case sDataType
Case "Text"
fConvertDatatypes = vbString
Case "Int", "TinyInt", "Integer"
fConvertDatatypes = vbInteger
Case "Date"
fConvertDatatypes = vbDate
Case "Currency"
fConvertDatatypes = vbCurrency
Case "Byte"
fConvertDatatypes = vbByte
Case "Long"
fConvertDatatypes = vbLong
End Select
End Function
Private Function fGetClientTableName(clientid As Integer)
MsgBox (clientid)
End Function
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
Adam Turner wrote: You forgot to answer this part:
"Perhaps, you could post some of your best coding/programming examples
[quoted text clipped - 3 lines]Web Searches for "adam turner" program and "adam turner" code did not
reveal these to me. "
Sure...here's a current project that should be up everyone's alley (it's
somewhat lengthy):
Private Sub cmdDatabase_Click()
'Reference the following
'*Microsoft ADO Ext. 2.1 for DDL and Security
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\aturner\Desktop\Batch Tester5\
BogusTestBatch.mdb;"
End Sub
Private Sub cmdCreateTables_Click()
Dim MyDatabase As Database
Dim Submission As TableDef
Dim SubmitClient000015 As TableDef
Dim ProductClient000015 As TableDef
Dim sDataType As String
Dim cn As ADODB.Connection
Dim cmd As Command
Dim rstFields As ADODB.Recordset
Dim sFieldName, sType As String
Dim iLength, iOfferTableID, iDisplayed As Integer
Dim iSubmission, iSpecial, iProduct As Integer
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
With cn
.ConnectionString = "Provider=SQLOLEDB;Integrated Security=SSPI;Data
Source=gfssvr15;Initial Catalog=GFS;"
.CursorLocation = adUseClient
.Mode = adModeRead
.Open
End With
With cmd
.CommandText = "SELECT o.FieldName, o.Type, o.Length, o.OfferTableID, o.
Displayed, oc.ClientID " & _
"FROM DBO.OfferDictionary o(NOLOCK) " & _
"JOIN DBO.OfferClient oc(NOLOCK) ON o.OfferID = oc.
OfferID " & _
"WHERE o.OfferID = 22624 " & _
"ORDER BY o.OfferTableID"
.CommandType = adCmdText
.ActiveConnection = cn
End With
Set rstFields = cmd.Execute
iSubmission = rstFields.Fields(3).Value
iSpecial = iSubmission + 1
'iProduct = iSubmission + 2
'open database
Set MyDatabase = OpenDatabase(App.Path + "\BogusTestBatch.mdb")
Call fGetClientTableName(rstFields.Fields(5).Value) 'Pass clientid to
check for client level tables and return table name
'create the table
Set Submission = MyDatabase.CreateTableDef("Submission")
Set SubmitClient000015 = MyDatabase.CreateTableDef("SubmitClient000015")
Set ProductClient000015 = MyDatabase.CreateTableDef("ProductClient000015")
On Error Resume Next
'delete the table if it already exists
MyDatabase.TableDefs.Delete Submission.Name
MyDatabase.TableDefs.Delete SubmitClient000015.Name
MyDatabase.TableDefs.Delete ProductClient000015.Name
Do While Not rstFields.EOF
sFieldName = rstFields.Fields(0).Value 'Fields(0).Value =
Fieldname
sType = rstFields.Fields(1).Value 'Fields(1).Value = Type
iLength = rstFields.Fields(2).Value 'Fields(2).Value = Length
iOfferTableID = rstFields.Fields(3).Value 'Fields(3).Value =
OfferTableID
iDisplayed = rstFields.Fields(4).Value 'Fields(4).Value =
Displayed
Select Case iOfferTableID 'add fields to the tables
Case iSubmission 'ORDER BY OfferTableID in SELECT statment is the
key
With Submission
If sFieldName = "RecordID" Then
.Fields.Append .CreateField("RecordID", PrimaryKey,
10)
Else
.Fields.Append .CreateField(sFieldName,
fConvertDatatypes(sType), iLength)
End If
End With
'pAddRecords (iDisplayed)
Case iSpecial
With SubmitClient000015
.Fields.Append .CreateField(sFieldName, fConvertDatatypes
(sType), iLength)
End With
'pAddRecords (iDisplayed)
Case Else 'there's not always a product table
With ProductClient000015
.Fields.Append .CreateField(sFieldName, fConvertDatatypes
(sType), iLength)
End With
'pAddRecords (iDisplayed)
End Select
'NewTable.Indexes.Refresh
MyDatabase.TableDefs.Append Submission
MyDatabase.TableDefs.Append SubmitClient000015
MyDatabase.TableDefs.Append ProductClient000015
rstFields.MoveNext
Loop
'Debug.Print rstFields.RecordCount
MyDatabase.Close 'close database
Set MyDatabase = Nothing
cn.Close 'close connection
Set cn = Nothing
'Call MsgBox("Tables Created Successfully", vbOKOnly)
End Sub
Private Sub pAddRecords(iDisplayed As Boolean)
'Displayed needs populated
'must get retailer from offerdictionary
'get sku or model if available
End Sub
Private Function fConvertDatatypes(sDataType As String)
Select Case sDataType
Case "Text"
fConvertDatatypes = vbString
Case "Int", "TinyInt", "Integer"
fConvertDatatypes = vbInteger
Case "Date"
fConvertDatatypes = vbDate
Case "Currency"
fConvertDatatypes = vbCurrency
Case "Byte"
fConvertDatatypes = vbByte
Case "Long"
fConvertDatatypes = vbLong
End Select
End Function
Private Function fGetClientTableName(clientid As Integer)
MsgBox (clientid)
End Function
Ofcourse this code is incomplete and would be a lot easier to read in the VB6
with the pretty highlighted keywords...but it basically grabs selected tables
from the SQL Server and auto generates an .mdb file with 3 new tables.
Why? Honestly, I've been a programmer for the #1 fulfillment company in the
US (Global Fulfillment Services) for the past year and it's taken me that
long to learn their in-house server/table structure. It is severely intricate
and insanely brilliant. The entire infrastructure is a work of art. (No
thanks to me. I just use it to write stand alone apps.)
If anyone has any questions on this code, feel free to question or criticize.
I'm always open for "constructive" critcizm.
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
How is the ADO catalog, "cat" used?
Why do you create TableDefs with DAO instead of creating a SQL (DDL)
"CREATE TABLE ..." string and executing it?
"lylefair" <ly***********@aim.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com: How is the ADO catalog, "cat" used?
Why do you create TableDefs with DAO instead of creating a SQL
(DDL) "CREATE TABLE ..." string and executing it?
I didn't look at the code, but Jet DDL doesn't handle all the Access
properties that DAO can deal with.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
lylefair wrote: How is the ADO catalog, "cat" used?
Here is the object model:
http://msdn.microsoft.com/library/de...objcatalog.asp
lylefair wrote:Why do you create TableDefs with DAO instead of creating a SQL (DDL)
"CREATE TABLE ..." string and executing it?
Although the code doesn't reflect this, the table names dynamically change
based on the clientid return from the first SELECT query. Using a hard coded
"CREATE TABLE..." approach would require a moderate and unnecessary level of
string concatenation to determine the table name in opposition of passing the
clientid to a function and having it return the table name.
The tablenames have a consistent name convention as follows:
Client-level tables = all fields will always be the same
-Will always have the same naming convention
------------------------------------------------------------------------------
----------
Table 1: Submission
Table 2: SubmitClient000 + Clientid (e.g. SubmitClient00015)
Table 3: ProductClient000 + Clientid (e.g. ProductClient00015)
Offer-Level tables = Most feilds will be remain the same, but may vary
-Will always be the same namingn convention but have an inconsistent number
concatenation
------------------------------------------------------------------------------
----------
Table1: Submission
Table 2: Submit00 + OfferID (e.g. Submit0010256)
Table 3: Product00+ OfferID (e.g. Product0010256)
Hence,
'create the table
Set Submission = MyDatabase.CreateTableDef("Submission")
Set SubmitClient000015 = MyDatabase.CreateTableDef("SubmitClient000015")
Set ProductClient000015 = MyDatabase.CreateTableDef("ProductClient000015")
Would instead be...
'create the table
Set Submission = MyDatabase.CreateTableDef("Submission") *Always has the
same name
Set SubmitClient000015 = MyDatabase.CreateTableDef(fGetTableName(clientid,
1))
Set ProductClient000015 = MyDatabase.CreateTableDef(fGetTableName(clientid,
2))
Private Function fGetTableName(clientid as integer, id as integer) AS String
Select Case clientid
Case 1
fGetTableName = "SubmitClient00" & clientid
Case 2
fGetTableName = "ProductClient00" & clientid
End Function
Hope this answers your question
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
Oops...I meant
Private Function fGetTableName(clientid as integer, id as integer) AS String
Select Case id 'Not clientid
Case 1
fGetTableName = "SubmitClient00" & clientid
Case 2
fGetTableName = "ProductClient00" & clientid
End Function
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
Adam Turner wrote: Oops...I meant
Private Function fGetTableName(clientid as integer, id as integer) AS String
Select Case id 'Not clientid
Case 1
fGetTableName = "SubmitClient00" & clientid
Case 2
fGetTableName = "ProductClient00" & clientid
End Function
Also...I didn't think to add...
---The numeric value is fixed at 6 digits and clientid's are variable lengths.
This is why a seperate function is needed.
Hence....the funtion would look like this
Private Function fGetTableName(ClientID, ID as Integer)
Select Case ID
Case 1
Select Case Len(ClientID)
Case 1
fGetTableName = "SubmitClient00000" & ClientID
Case 2
fGetTableName = "SubmitClient0000" & ClientID
Case 3
fGetTableName = "SubmitClient000" & ClientID
Case Else
Etc....
End Select
Case 2
Case 1
fGetTableName = "ProductClient00000" & ClientID
Case 2
fGetTableName = "ProductClient0000" & ClientID
Case 3
fGetTableName = "ProductClient000" & ClientID
Case Else
Etc....
End Select
End Select
End Function
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
I really am familiar with the ADOX object model. Perhaps, you
misunderstood my question. I wanted to know how the ADOX cat was used
in your code. I can find another reference to it after you create it.
I don't how you can post working exemplary code and then revise it
twice?
Does JET handle any ACCESS properties?
"lylefair" <ly***********@aim.com> wrote in
news:11**********************@z14g2000cwz.googlegr oups.com: Does JET handle any ACCESS properties?
Yes, DAO can get to them through the Properties collection.
So far as I'm aware, DDL cannot create or alter Access-only
properties (which are implemented as custom properties, in a certain
sense),
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
"Adam Turner via AccessMonster.com" <u14766@uwe> wrote in
news:55e554aa81abc@uwe: Private Function fGetTableName(ClientID, ID as Integer)
I don't know exactlyi what you're trying to accomplish with this
code, but leaving out parameter type declarations doesn't seem to me
to be one of the hallmarks of good code. If you want a variant,
declare it as a variant.
I also don't understand why you'd use a SELECT CASE when all you
really need is to format the ID number with appropriate padding of
zeros. Seems to me that:
Dim strTable As String
SELECT CASE ID
CASE 1
strTable = "SubmitClient"
CASE 2
strTable = "ProductClient"
END SELECT
fGetTableName = strTable & Format(ClientID, "000000")
or something similar would do what your 21 lines of code do
(actually 23 lines since you omitted the Len() SELECT CASE in the
CASE 2 block).
Lastly, I don't think it's an exemplary coding practice to
implicitly coerce an numeric value to a string, as you do when you
test the length of ClientID. Of course, I am making certain
assumptions about ClientID, that it's a numeric data type (probably
a long, since most fields called ID are going to be auto incremented
numbers (either a Jet Autonumber, or the corresponding automatically
incremented value from the back end of your choice).
Of course, I see that the code this is supposed to replace defined
ClientID as Integer, which in VBA is an awfully narrow data type,
and which ought to be coerced to a string before you test its
length. Indeed, Len() does not implicitly coerce data types.
If this is exemplary code, as opposed to stanadard air code, then
it's not up to my standards of good code.
Indeed, I don't even consider it satisfactory air code!
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
David W. Fenton wrote: Private Function fGetTableName(ClientID, ID as Integer)
I don't know exactlyi what you're trying to accomplish with this
code, but leaving out parameter type declarations doesn't seem to me
to be one of the hallmarks of good code. If you want a variant,
declare it as a variant.
I also don't understand why you'd use a SELECT CASE when all you
really need is to format the ID number with appropriate padding of
zeros. Seems to me that:
Dim strTable As String
SELECT CASE ID
CASE 1
strTable = "SubmitClient"
CASE 2
strTable = "ProductClient"
END SELECT
fGetTableName = strTable & Format(ClientID, "000000")
or something similar would do what your 21 lines of code do
(actually 23 lines since you omitted the Len() SELECT CASE in the
CASE 2 block).
Lastly, I don't think it's an exemplary coding practice to
implicitly coerce an numeric value to a string, as you do when you
test the length of ClientID. Of course, I am making certain
assumptions about ClientID, that it's a numeric data type (probably
a long, since most fields called ID are going to be auto incremented
numbers (either a Jet Autonumber, or the corresponding automatically
incremented value from the back end of your choice).
Of course, I see that the code this is supposed to replace defined
ClientID as Integer, which in VBA is an awfully narrow data type,
and which ought to be coerced to a string before you test its
length. Indeed, Len() does not implicitly coerce data types.
If this is exemplary code, as opposed to stanadard air code, then
it's not up to my standards of good code.
Indeed, I don't even consider it satisfactory air code!
Ok David,
Please write the function given the following information.
1. 3 tables will be created
2. Table 1 will always be called "Submission"
3. Tables 2 & 3 will always begin with "Submit" & "Product"
4. They will always be followed by 6 numbers in length which is the clientid
preceeded by zeros
--The clientid will change with each execution of the program
--The program is also designed to process more than one submission
dynamically
5. The clientid can range from 11 to 999999 (we don't have clientid's < 11)
David W. Fenton wrote: Private Function fGetTableName(ClientID, ID as Integer)
I don't know exactlyi what you're trying to accomplish with this
code, but leaving out parameter type declarations doesn't seem to me
to be one of the hallmarks of good code. If you want a variant,
declare it as a variant.
This is petty and not worth a response.
David W. Fenton wrote:I don't know exactlyi what you're trying to accomplish with this
code,
Then how can you critique it? I'm uncertain if your intention is questioning
the purpose of the code or simply to take any stab in the dark in attempts to
expose some ego driven appetite. In either case, please understand the code
fully before you try to pick apart the minor syntax.
David W. Fenton wrote:Indeed, I don't even consider it satisfactory air code!
Your code or mine?
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
lylefair wrote: I really am familiar with the ADOX object model. Perhaps, you
misunderstood my question. I wanted to know how the ADOX cat was used
in your code. I can find another reference to it after you create it.
I don't how you can post working exemplary code and then revise it
twice?
I apologize for misinterpreting your question. All the ADOX is doing is
creating the .mdb file. Nothing more than that. There's no deeper meaning
behind it. I just googled a "Create .mdb file" search and copied the code.
Lastly, the code I posted is a current project with 4 or 5 versions. I have 3
copies at home, and 3 copies at work and the home versions I can't test. I
don't have a connection to the SQL Server so I apologize for any minor syntax
errors which are, in most cases, irrelevant.
--
Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200510/1
Adam Turner wrote: Private Function fGetTableName(ClientID, ID as Integer)
[quoted text clipped - 38 lines]
Indeed, I don't even consider it satisfactory air code!
Ok David,
Please write the function given the following information.
1. 3 tables will be created
2. Table 1 will always be called "Submission"
3. Tables 2 & 3 will always begin with "Submit" & "Product"
4. They will always be followed by 6 numbers in length which is the clientid
preceeded by zeros
--The clientid will change with each execution of the program
--The program is also designed to process more than one submission
dynamically
5. The clientid can range from 11 to 999999 (we don't have clientid's < 11)
Private Function fGetTableName(ClientID, ID as Integer)
I don't know exactlyi what you're trying to accomplish with this
code, but leaving out parameter type declarations doesn't seem to me
to be one of the hallmarks of good code. If you want a variant,
declare it as a variant.
This is petty and not worth a response.
I don't know exactlyi what you're trying to accomplish with this
code,
Then how can you critique it? I'm uncertain if your intention is questioning
the purpose of the code or simply to take any stab in the dark in attempts to
expose some ego driven appetite. In either case, please understand the code
fully before you try to pick apart the minor syntax.
Indeed, I don't even consider it satisfactory air code!
Your code or mine?
David, I'm still waiting for your response?
--
Message posted via http://www.accessmonster.com This discussion thread is closed Replies have been disabled for this discussion. Similar topics
5 posts
views
Thread by Tony Clarke |
last post: by
|
2 posts
views
Thread by RJ |
last post: by
|
4 posts
views
Thread by serge |
last post: by
|
3 posts
views
Thread by serge |
last post: by
|
8 posts
views
Thread by doomx |
last post: by
|
9 posts
views
Thread by laredotornado |
last post: by
|
4 posts
views
Thread by Ward Bekker |
last post: by
|
reply
views
Thread by KDB |
last post: by
| | | | | | | | | | | | | | | | | | | | | |
