Photo Gallery Member List Search Calendars FAQ Ticket List Log Out


VBS from Access to SQL Server

  		Logged in as: Guest
arrSession:exec spGetSession 2,2,52110
Active Users: There are 0 members and 0 guests.
Users viewing this topic: none
 

 

  
 
  Printable Version
All Forums >> [Scripting] >> WSH & Client Side VBScript >> VBS from Access to SQL Server
  Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks! 		Page: [1]
Login
Message << Older Topic   Newer Topic >>
VBS from Access to SQL Server - 9/21/2007 5:24:08 AM   
  josborn

 

Posts: 6
Score: 0
Joined: 9/21/2007
Status: offline 		Hello All,
I am having an issue and was hoping you could help me.  I have the velow script which connects and inserts into an Access DB.  I just need to make it connect and insert into a SQL DB instead.  The schema is identical.  Thanks in advance for any help you can give me.  Keep up the good work!!!

Joe

Dim oAccess
Const ADS_SCOPE_SUBTREE = 2
Const NoDecimals = 0
Const Decimals = 2
Const ForReading = 1
Currentdate = Date
Set objDictionary = CreateObject("Scripting.Dictionary")
db = "C:\FreeSpace.mdb"
tablename = "Space"
dtDate = "dtDate"
ServerName = "ServerName"
Drive = "Drive"
TotalSpace = "TotalSpace"
UsedSpace = "UsedSpace"
FreeSpace = "FreeSpace"
PerFree = "PerFree"
On error resume next
Set WshShell = CreateObject("WScript.Shell")
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCOmmand.ActiveConnection = objConnection
Set oAccess = CreateObject("Access.Application")
oAccess.OpenCurrentDatabase db
oAccess.DoCmd.OpenTable(tablename)
'Retrieve all Domain Controllers from Domain
objCommand.CommandText = "Select Name from 'LDAP://OU=Servers,DC=comain,DC=com' Where operatingSystem = '*' Order by name"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 30
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.Properties("Cache Results") = True
Set objRecordSet = objCommand.Execute   'Execute Query
'objRecordSet.MoveFirst
Do Until objRecordSet.EOF
server = objRecordSet.Fields("Name").Value
wscript.echo server
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & server & "\root\cimv2")
Set colDisks = objWMIService.ExecQuery _
("SELECT * FROM Win32_LogicalDisk WHERE DriveType = 3")
For Each objDisk in colDisks
TotalSpace =( objDisk.Size / 1073741824 )
perfree =( objDisk.FreeSpace / objDisk.Size )
FreeSpace =( objDisk.FreeSpace / 1073741824 )
UsedSpace = ( TotalSpace - FreeSpace )
'  SQLstmt = "INSERT INTO " & tableName & " (" & LockOutDate & "," & AuthenticationServer & "," & samAccountName & "," & ClientMachine & ") VALUES ('"
'  SQLstmt = SQLstmt & dtmTimeGenerated.GetVarDate("False") &"','" & objEvent.ComputerName & "','" & strSAMAccountName & "','" & strClientMachine & "')"
SQLstmt = "INSERT INTO " & tableName & " (dtDate,ServerName,Drive,TotalSpace,UsedSpace,FreeSpace,PerFree) VALUES ('"
SQLstmt = SQLstmt & currentdate & "','" & server & "','" & objDisk.DeviceID & "','" & TotalSpace & "','" & UsedSpace & "','" & FreeSpace & "','" & PerFree & "')"
'  SQLstmt = "INSERT INTO " & tableName & " (" & dtDate & ") VALUES ('" & currentdate & "')"
oAccess.DoCmd.RunSQL(SQLstmt)
'  If perfree < "0.2"  Then
'   Set objEmail = CreateObject("CDO.Message")
'   objEmail.From = "emailaddress"
'   objEmail.To = "emailaddress"
'   objEmail.Subject = server &" has 20% HDD Space available on "& objdisk.DeviceID
'   objEmail.Textbody = "Free Disk Space:.." & FormatNumber(FreeSpace, Decimals) &" Gigs"& VBCRLF &"Percent Free:....." & FormatPercent(perfree, NoDecimals)
'   objEmail.Configuration.Fields.Item _
'   ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'   objEmail.Configuration.Fields.Item _
'   ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "server"
'       
'   objEmail.Configuration.Fields.Item _
'   ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
'   objEmail.Configuration.Fields.Update
'   objEmail.Send
'   End if
Next
TotalSpace = 0
perfree  = 0
FreeSpace = 0
UsedSpace = 0
objRecordSet.MoveNext
Loop
oAccess.DoCmd.Close
oAccess.DoCmd.Quit
Set oAccess = Nothing

< Message edited by josborn -- 9/21/2007 5:26:05 AM >
 
 
Post #: 1
 
RE: VBS from Access to SQL Server - 9/21/2007 7:07:26 AM   
  josborn

 

Posts: 6
Score: 0
Joined: 9/21/2007
Status: offline 		Sorry to be a pest, but, anyone?  anyone?  :-)

Joe

(in reply to josborn)
 
 
Post #: 2
 
RE: VBS from Access to SQL Server - 9/21/2007 7:28:36 AM   
  ehvbs

 

Posts: 2223
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		Hi josborn,

the good way would be to use ADO, connect to the SQL Server, create commands for
the insertions, loop over your WMI results, set the parameters for the commands,
and execute them. This looks like a complete rewrite of your script.

A quick and dirty hack would be to change your insert statements from

  INSERT INTO tablename (fields) VALUES ( values )

To

  INSERT INTO tablename IN <external database> (fields) VALUES ( values )

<external database> would be your SQL DB.

What way would you prefer?

(in reply to josborn)
 
 
Post #: 3
 
RE: VBS from Access to SQL Server - 9/24/2007 12:02:42 AM   
  josborn

 

Posts: 6
Score: 0
Joined: 9/21/2007
Status: offline 		Thanks!!!  Quite honestly I was hoping I could simply somehow change the application.access to something else that would work with SQL.  I inherited this from a guy who just quit.  I am not real strong in vbs unfortunately...

(in reply to ehvbs)
 
 
Post #: 4
 
RE: VBS from Access to SQL Server - 9/24/2007 12:08:37 AM   
  josborn

 

Posts: 6
Score: 0
Joined: 9/21/2007
Status: offline 		And the quicker and dirtier the better.

(in reply to ehvbs)
 
 
Post #: 5
 
RE: VBS from Access to SQL Server - 9/24/2007 12:29:17 AM   
  ehvbs

 

Posts: 2223
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		Hi josborn,

on second thought I came up with an idea even more dirty: Can you take your
"C:\FreeSpace.mdb", rename the table "Space" to "Space_Old", use File|External
Data|Link Table (translate from German) to link to MS SQL table (the link should
be named "Space") and run a test?

If it fails: can you establish a ODBC connection to your MS SQL database?

Good luck!

ehvbs

(in reply to josborn)
 
 
Post #: 6
 
RE: VBS from Access to SQL Server - 9/24/2007 12:31:58 AM   
  josborn

 

Posts: 6
Score: 0
Joined: 9/21/2007
Status: offline 		I would much rather use an odbc, but I am trying the access linked table now...

(in reply to ehvbs)
 
 
Post #: 7
 
RE: VBS from Access to SQL Server - 9/24/2007 12:46:54 AM   
  ehvbs

 

Posts: 2223
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		Given a valid ODBC connection to your MS SQL DB, your Insert statement(s) should be changed
from something like this:

SQLstmt = "INSERT INTO " & tableName & " (dtDate,...

To

SQLstmt = "INSERT INTO [ODBC;DSN=<YorDsnName>]." & tableName & " (dtDate, ...

But using a OLEDB connection to your MS SQL DB and setting up some proper ADODB.Commands
would be better.

(in reply to josborn)
 
 
Post #: 8
 
RE: VBS from Access to SQL Server - 9/24/2007 1:12:51 AM   
  josborn

 

Posts: 6
Score: 0
Joined: 9/21/2007
Status: offline 		This does not seem to be working...  The linked table in access does however.  I would really like to get rid of the access db all together...  Thanks!



 SQLstmt = "INSERT INTO[ODBC;DSN=DiskSpace]." & tableName & " (dtDate,ServerName,Drive,TotalSpace,UsedSpace,FreeSpace,PerFree) VALUES ('"
 SQLstmt = SQLstmt & currentdate & "','" & server & "','" & objDisk.DeviceID & "','" & TotalSpace & "','" & UsedSpace & "','" & FreeSpace & "','" & PerFree & "')"
 oAccess.DoCmd.RunSQL(SQLstmt)

(in reply to ehvbs)
 
 
Post #: 9
 
RE: VBS from Access to SQL Server - 9/24/2007 1:24:30 AM   
  ehvbs

 

Posts: 2223
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		Maybe:

SQLstmt = "INSERT INTO [ODBC;DSN=DiskSpace]." & tableName & " (dtDate, ..

(mark the space after INTO)

It could cost some effort/time to do it properly!

(in reply to josborn)
 
 
Post #: 10
 
 
 
 

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.
All Forums >> [Scripting] >> WSH & Client Side VBScript >> VBS from Access to SQL Server Page: [1]
Jump to:





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
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts




Forum Software © ASPPlayground.NET Advanced Edition 2.5.5 ANSI