RE: VB primer help ?
I'm just adding a few points to clarify Ian's excellent example -
A. Ian's example uses the Oracle provider. For Access you need:
PROVIDER=Microsoft.Jet.OLEDB.4.0
B. Access has problems with some field names unless you enclose them in
square braces.
Also be careful about delimiters around the data
e.g.
INSERT INTO [MyTableName] ([AString],[A Number],[ADate]) VALUES ('Some
Value',SomeNumber,#Some Date#)
C. You can do the whole thing in VBScript including reading the import file.
However this is a little non-obvious - you need to instantiate a
Scripting.FileSystemObject along the lines of:
set fso = CreateObject("scripting.filesystemobject")
Set F = FSO.OpenTextFile(fn, 1)
Do while not F.AtEndOfStream
s = F.ReadLine
MyFields = split(s,MyDelimiter)
' do some processing here
Loop
F.Close
I'll let you work out the rest yourself!
Brian Leach
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Ian McGowan
> Sent: 05 March 2004 06:56
> To: 'U2 Users Discussion List'
> Subject: RE: VB primer help ?
>
> -Original Message-
> >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Maybe one of
> you has a
> >trivial example of updating a row in a VB macro? All the examples I
> >can find are doing it using a macro EXTERNAL to the
> database, when the
> >one I'm using is run from inside the project... did that make sense?
>
> is there any reason you can't use ODBC or OLEDB and just
> issue an update statement? that would seem the most direct
> route. this link has an example of the connection string to
> use for access:
>
> http://www.asp101.com/tips/index.asp?id=98
>
> and here's a vbscript example that inserts a bunch of rows.
> you would want to loop over your flat file reading a line at
> a time, issuing updates like:
>
> sql = "update mytable set fieldx = '" & valuex & "' where"
> sql = sql & " primarykey = '" & mykey & "'"
> con.execute(sql)
>
>
> UID = "system1"
> PWD = "***"
> Service = "marin"
>
> Set oAux = CreateObject("Sys1Aux.CAux")
> Set Con = CreateObject("ADODB.Connection") Set rs =
> CreateObject("ADODB.RecordSet") Con.Open(
> "PROVIDER=MSDAORA;DATA SOURCE=" & Service & ";USER ID=" & UID
> & ";PASSWORD=" & PWD)
>
> for i = 1 to 1
> sql="insert into trin_temp values ('" & i & "','" &
> GUIDGen(oAux) & "')"
> wscript.echo sql
> con.execute (sql)
> next
>
> Function GUIDGen(Aux)
> Dim sGUID
> sGuid = Aux.GuidGen
> ' Get rid of the cute curly stuff
> sGUID = Mid(sGUID, 2, Len(sGUID) - 2)
> GUIDGen = sGUID
> End Function
> --
> -
> --
> u2-users mailing list
> [EMAIL PROTECTED]
> http://www.oliver.com/mailman/listinfo/u2-users
>
> __
> __
> This email was checked by MessageLabs SkyScan before entering
> Microgen.
This email was checked on leaving Microgen for viruses, similar
malicious code and inappropriate content by MessageLabs SkyScan.
DISCLAIMER
This email and any attachments are confidential and may also be
privileged.
If you are not the named recipient, please notify the sender
immediately and do not disclose the contents to any other
person, use it for any purpose, or store or copy the information.
In the event of any technical difficulty with this email, please
contact the sender or [EMAIL PROTECTED]
Microgen Information Management Solutions
http://www.microgen.co.uk
--
u2-users mailing list
[EMAIL PROTECTED]
http://www.oliver.com/mailman/listinfo/u2-users
RE: VB primer help ?
> In a universe database I have a file Customers. > In an Access database I have some of the fields of that same file > as well, populated from the source universe file via a flat file > transport and import. Have you not got ODBC available? > Now however I need to just update those rows that have changed. If you can link or import the dumpfile to Access then use the Query tool (wizard thing) to build an update query, you should be right. Unless you are doing something other than overwriting based on the primary key, you shouldn't need any code really. If you were to import the whole table from UV to Access you can use the New Query/Find Unmatched wizard to find differences. Hope that helps, Stuart ** This email message and any files transmitted with it are confidential and intended solely for the use of addressed recipient(s). If you have received this email in error please notify the Spotless IS Support Centre (61 3 9269 7555) immediately who will advise further action. This footnote also confirms that this email message has been scanned for the presence of computer viruses. ** -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: VB primer help ?
-Original Message-
>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
>Maybe one of you has a trivial example of updating a row
>in a VB macro? All the examples I can find are doing it
>using a macro EXTERNAL to the database, when the one I'm
>using is run from inside the project... did that make sense?
is there any reason you can't use ODBC or OLEDB and just
issue an update statement? that would seem the most direct
route. this link has an example of the connection string
to use for access:
http://www.asp101.com/tips/index.asp?id=98
and here's a vbscript example that inserts a bunch of rows.
you would want to loop over your flat file reading a line at
a time, issuing updates like:
sql = "update mytable set fieldx = '" & valuex & "' where"
sql = sql & " primarykey = '" & mykey & "'"
con.execute(sql)
UID = "system1"
PWD = "***"
Service = "marin"
Set oAux = CreateObject("Sys1Aux.CAux")
Set Con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.RecordSet")
Con.Open( "PROVIDER=MSDAORA;DATA SOURCE=" & Service & ";USER ID=" & UID &
";PASSWORD=" & PWD)
for i = 1 to 1
sql="insert into trin_temp values ('" & i & "','" & GUIDGen(oAux) &
"')"
wscript.echo sql
con.execute (sql)
next
Function GUIDGen(Aux)
Dim sGUID
sGuid = Aux.GuidGen
' Get rid of the cute curly stuff
sGUID = Mid(sGUID, 2, Len(sGUID) - 2)
GUIDGen = sGUID
End Function
---
--
u2-users mailing list
[EMAIL PROTECTED]
http://www.oliver.com/mailman/listinfo/u2-users
Re: VB primer help ?
In a universe database I have a file Customers. In an Access database I have some of the fields of that same file as well, populated from the source universe file via a flat file transport and import. Now however I need to just update those rows that have changed. So in Universe I already have a way (I think) to find just what's changed and make a flat file. So now I want to just load those changes to the Access database. So I figured out how to open a macro and played around with the VB language somewhat but I wonder if someone could give me a few lines of help. I can already open the flat file in VB and read a line out of it. I can also open the access database in VB and read a row/field out of that. I figured that out so far. The flat file will have as its first field the primary key which is already setup to match between the uv and access files. So what I want to do is select the row with that key, which I already know how to do. But the part I'm getting stuck on is how exactly do you do the update? I tried something like this file!field = newvalue file.update and it complains along the lines of "that process cannot do that function" or something like that. Maybe one of you has a trivial example of updating a row in a VB macro? All the examples I can find are doing it using a macro EXTERNAL to the database, when the one I'm using is run from inside the project... did that make sense? Anyway... Will -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
