Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Other Programming > VBScript
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 26th, 2004, 06:23 AM
Registered User
 
Join Date: Nov 2004
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
 
Join Date: Mar 2004
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
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
 
Join Date: Mar 2004
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
 
Join Date: Mar 2004
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





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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.