Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Other Programming > VBScript
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VBScript For questions and discussions related to VBScript.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VBScript section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 26th, 2004, 06:23 AM
Registered User
 
Join Date: Nov 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Importing text data to an access table

I have created a script which retrieves system information like serial number, I.P. address, computer name etc and it creates an individual txt file for each PC with the computer name being the text file name.

I need to import this information into an access database (2003) and want it automated as there are alot of files. I haven't found a clear way to do this using vb script, or would it be easier doing it from within access itself?
Reply With Quote
  #2 (permalink)  
Old November 26th, 2004, 11:08 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

   I report the same sort of data to a SQL server instead of individual text files just because pulling them in manually is so time consuming.

   Can you make the script dump the data directly into your access database?

   Here is the pertinent part of the script I use for the SQL Server dump:

'================================================= ==
strUserName = "username"
strPassword = "password"
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

'open connection using system dsn, create recordset from tblComputer
objConnection.Open "DSN=AssetManagement;", strUserName, strPassword
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM tblComputer" , objConnection, adOpenStatic, adLockOptimistic

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem",,48)

'retrieves basic system info
For Each objItem In colItems
    objRecordset.AddNew 'starts to add record to tblComputer
        objRecordset("ComputerName") = objItem.Caption
    objRecordset("Container") = objItem.Domain
        objRecordset("Manufacturer") = objItem.Manufacturer
        objRecordset("Model") = objItem.Model
        '.....................
        objRecordset.Update 'finally updates record in tblComputer
Next

objRecordset.Close
objConnection.Close
'================================================= =

You could also dump the data into an Excel spreadsheet, and then link the spreadsheet as a table to your database. Putting the data into the spreadsheet is only slightly more complicated than creating individual text files. You need to have the spreadsheet on a drive that every computer can see.

In any event, whatever script you write to pull the data from the text files to Access can just as easily be adapted to push the data directly from your script into Access (skip the text files.)

I hope this helps.



mmcdonal
Reply With Quote
  #3 (permalink)  
Old November 27th, 2004, 11:52 AM
Registered User
 
Join Date: Nov 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Don't suppose you could provide me with the script to dump the data into a excel spreadsheet? Am just a novice at vb script and have only started using it in the last week, so you could help that would be great.

Reply With Quote
  #4 (permalink)  
Old November 29th, 2004, 01:47 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I think I could help you get the data into an Access file. Will that work? Just pretend it's Excel, like most users do. ;)

mmcdonal
Reply With Quote
  #5 (permalink)  
Old November 29th, 2004, 01:57 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

This code should get you started. You will need to create a system DSN on each machine to get it to report to your database. I can give you code to do that.

You can run code throughout this connection, so you can gather data using various script components, and then come back to this connection and add data to the table since it is not updated until you use the Update line.

Each time you need a new table, you have to close the existing connection and reopen another one using a different query.

'=======================================
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")

objConn.Open "DSN=YourDSNNameHERE;"
objRS.CursorLocation = 3
objRS.Open "SELECT * FROM YOURTABLE", objConn, 3, 3

objRS.AddNew

'start your asset queries here.
'everytime you capture an asset name or type, pass it to
'a variable, and use this sort of line...

objRS("FieldName") = strYourVariable

'when you are done getting data, update the table (record)

objRS.Update

objRS.Close
objConn.Close
'==========================================

mmcdonal
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing from Excel to Access table DeborahP Excel VBA 3 April 3rd, 2007 03:21 AM
Importing Hyperlinks into Access table Sammy8932 Access VBA 0 May 11th, 2005 09:23 AM
Importing data from a text file nav PHP How-To 2 March 7th, 2005 02:42 PM
Problem importing data into a TEXT field seansheds SQL Server DTS 1 August 19th, 2004 02:52 PM
BCP error while importing data from a text file happygv SQL Server ASP 1 December 15th, 2003 09:16 AM



All times are GMT -4. The time now is 07:26 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.