Some time ago, with a lot of great help from this forum, I wrote a script that renames files that I've exported from our Hummingbird Document Management System.
The best I could get it to do when I had to append extra info to the new filename was offer InputBoxes for the user to type this information in (after reading it on the Hummingbird screen).
I've now been given ODBC access to the Humminbird back end, so I'd like to be able to look up this extra info directly from there. I've written an Access query that pulls the data live from Humminbird with the fields I need appended.
Can I modify my script to pull this data from my Access query (or preferably code in a direct connection to the ODBC source and pull it out that way)?
Part of my existing script extracts a number from the old filename and stores it in a variable. This number is the internal Hummingbird ID number for that file, which is now the 1st field in my query, so I'm hoping to be able to simply use this number to interrogate the Access db/ODBC source, pull out that extra 3 fields I need related to that ID number, then append it to my new file name.
This is what I've been aiming for for 2 years, it would be Nirvana and I'd die a happy man if I could get this working. Thanks.
I could post my whole script, but it's 400+ lines, and 90% is not applicable to the above.
Thanks, even a pointer as to where to start would be good. Cheers
Yes you should be able to do this with no great effort. First you will need to figure out how to connect to the DB. Search around here and you should find some examples of connecting to a DB and running a query.
I tried that some time ago with another script and got very confused.
Thing is I have to be mega mega careful not to actually amend any data, I just want to read it and store it in variables.
Am i best using my script to open my Access query, or is it best to simply have the script connect to the ODBC directly?
Ideally I want to take what's stored in a variable, connect to the ODBC source, find that variable in a particular field in a table, then look up 3 related values in a different table, and return those 3 values to store in more variables.
I'm happy enough creating queries in Access (selecting the 2 tables I need, linking them on the common ID field, then pulling out the data I need), I just have no idea how to do the equivalent from a VB script.
Ok, I've had a play about, trying it the Access way first. I went into SQL view in my Access query, and copied the string. I then came up with the following VB script:
myVar is something I just inserted to prove to myself I could pass a variable into the SQL string. In my final script this would be the unique DocumentID in the Hummingbird table. I've then read the other 3 fields from the recordset and displayed them in a MsgBox, separated by dashes. The results of this MsgBox are EXACTLY what I want to append to the start of filename in my final script.
It would be great if you guys could help me get this working so that I don't have to go via Access to get the info.
Sorry I am not an SQL expert and I haven't an SQL server to test it on but it obviously only reads the data, so you should be safe to try it with your SQL string.
Thanks, I found that code too, but it doesn't work, it gives an error 80040E37 saying the first table name in the SQL statement is "Invalid object name". Obviously the SQL query line is AFTER the connection string, so the connection string works fine and lets me "log on".
< Message edited by markmcrobie -- 4/15/2008 10:56:30 PM >
Set objConnection = CreateObject("ADODB.Connection") Set objRecordSet = CreateObject("ADODB.Recordset") objConnection.Open "Provider=SQLOLEDB;Data Source=FAS-SQL1;Trusted_Connection=No;Initial Catalog=Berthing;User ID=DOCSUSER;Password=<mypassword>;" objRecordSet.Open "SELECT * FROM DOCSADM_CLUSTER;",objConnection, adOpenStatic, adLockOptimistic
This produces the "Invalid Object name" error on table DOCSADM_CLUSTER.
If I look at the information_table_schema table for the database, DOCSADM is in the TABLE_SCHEMA field and CLUSTER is in the TABLE_NAME field.
I've tried variations of DOCSADM_CLUSTER, and just CLUSTER, no luck.
One of the other tables in the SQL back end is information_table_privileges and all tables say the Grantor is DOCSADM (admin) and the grantee is DOCSUSER (users), but also has a field called IS_GRANTABLE which is set to NO for every table.
I should add that this all works perfectly when going through Access using the following code (I have the server, username, password, etc, set up in Control Panel > ODBC sources as a System DSN, then I add the tables as Linked in Access)
Is it maybe something to do with user name I'm logging in with not having authority to perform SELECT operations? (although I suspect not, since like I said I can get in via Access)
is that if I change Uid=DOCSUSER to something random, say FREDDY, I get an error on that line saying "Login failed for user ID FREDDY". If I use the correct username, the error is on the next line, the one with the SQL statement.
Well, I don't remember exactly where to get the info in Access. To be honest with you I dislike Access enough that I'm not in a hurry to open it and look for where to find it. Have you tried changing your connection string to use a trusted connection?
I have to be honest with you that the "Do It Right" part of me wants to make you figure out how to go directly to the SQL DB. But the "Make It Work" part says it is ok to go through Access to get it working now and you can figure out the right way later. Of course the "Real World" part of me knows that once it is working it is unlikely that later will ever come.
Success! I copied the SQL string from the SQL view in Access. For some reason Access labels the table OWNER_TABLENAME (i.e. DOCSADM_CLUSTER).
When trying to connect directly from my script without going through Access, if I change DOCSADM_CLUSTER to DOCSADM.CLUSTER, it works, using the following connection string:
1) I don't seem to have to use the Constants ", adOpenStatic, adLockOptimistic", i.e. it works without using them. Does this matter?
2) Does this mean I can delete the System DSN source in control panel, and it'll still work? (since I'm directly hard-coding the username, password, server, etc?
3) Am I right in using the following in my code at the end of my function?:
All Forums >> [Scripting] >> WSH & Client Side VBScript >> Getting data from ODBC or Access 
Printable Version
Getting data from ODBC or Access - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread