Connect to access database

Connect to access database

Generally I can get all my answers from the Windows 2000 Scripting guide. 
However, on the database part, they use a DSN which I do not want to use. I 
edited the code to what I thought was right but yet the script does not work, 
it just goes to the last line, so I think it is something in the connection 
sting where it is failing. My code is as follows:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.Recordset")
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;data 
source=F:\Inventory\DataBase\Test_Inventory.mdb"
oConn.Open sConn
oRS.CursorLocation = adUseClient

oRS.Open "SELECT * FROM INVENTORY", oConn, adOpenStatic, adLockOptimistic

' Loop through the recordset and determine if there is already a record. If 
so exit script,
' otherwise add new record
Do While NOT oRS.EOF
 If sMAC = oRS("MAC_ADDY") Then
  oRS.Close
  oCOnn.Close
  WScript.Quit
 Else
 MsgBox sUserName & vbCrLf & sManufacturer & " " & sModel & vbCrLf & 
sSerialNumber & vbCrLf & sIP & vbCrLf & sMAC & vbCrLf & sDomain 'Used only 
for testing
  oRS.AddNew
  oRS("SHORT") = sUserName
  oRS("PC_MODEL") = sModel
  oRS("PC_MAKE") = sManufacturer
  oRS("SERVICE_TAG") = sSerialNumber
  oRS("IP_ADDY") = sIP
  oRS("MAC_ADDY") = sMAC
  oRS("DOMAIN") = sDomain
  oRS.Update
 End If
Loop

oRS.Close
oConn.Close

MsgBox "Script Complete"

I know it is not connecting to the database because 1. it remains empty, and 
2. the msgbox within the If statement should popup, which it doesn't. Any 
help is appriciated.

date: Tue, 22 Jan 2008 18:03:22 -0800 author: Phyxious

Re: Connect to access database

Phyxious wrote:

> Generally I can get all my answers from the Windows 2000 Scripting guide.
> However, on the database part, they use a DSN which I do not want to use. 
> I
> edited the code to what I thought was right but yet the script does not 
> work,
> it just goes to the last line, so I think it is something in the 
> connection
> sting where it is failing. My code is as follows:
>
> Const adOpenStatic = 3
> Const adLockOptimistic = 3
> Const adUseClient = 3
>
> Set oConn = CreateObject("ADODB.Connection")
> Set oRS = CreateObject("ADODB.Recordset")
> sConn = "Provider=Microsoft.Jet.OLEDB.4.0;data
> source=F:\Inventory\DataBase\Test_Inventory.mdb"
> oConn.Open sConn
> oRS.CursorLocation = adUseClient
>
> oRS.Open "SELECT * FROM INVENTORY", oConn, adOpenStatic, adLockOptimistic
>
> ' Loop through the recordset and determine if there is already a record. 
> If
> so exit script,
> ' otherwise add new record
> Do While NOT oRS.EOF
> If sMAC = oRS("MAC_ADDY") Then
>  oRS.Close
>  oCOnn.Close
>  WScript.Quit
> Else
> MsgBox sUserName & vbCrLf & sManufacturer & " " & sModel & vbCrLf &
> sSerialNumber & vbCrLf & sIP & vbCrLf & sMAC & vbCrLf & sDomain 'Used only
> for testing
>  oRS.AddNew
>  oRS("SHORT") = sUserName
>  oRS("PC_MODEL") = sModel
>  oRS("PC_MAKE") = sManufacturer
>  oRS("SERVICE_TAG") = sSerialNumber
>  oRS("IP_ADDY") = sIP
>  oRS("MAC_ADDY") = sMAC
>  oRS("DOMAIN") = sDomain
>  oRS.Update
> End If
> Loop
>
> oRS.Close
> oConn.Close
>
> MsgBox "Script Complete"
>
> I know it is not connecting to the database because 1. it remains empty, 
> and
> 2. the msgbox within the If statement should popup, which it doesn't. Any
> help is appriciated.

I use a connection string similar to:
===============
strPath = "F:\Inventory\DataBase"
strDB = "Test_Inventory.mdb"

strConnect = "DRIVER=Microsoft Access Driver (*.mdb);" _
        & "FIL=MS Access;DriverId=25;DefaultDir=" _
        & strPath & ";DBQ=" & strPath & "\" & strDB & ";"

Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.ConnectionString = strConnect
adoConnection.Open

-- 
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

date: Tue, 22 Jan 2008 20:55:57 -0600 author: Richard Mueller [MVP]

Re: Connect to access database

"Richard Mueller [MVP]"  wrote in 
message news:uiTr9tWXIHA.3556@TK2MSFTNGP02.phx.gbl...
> Phyxious wrote:
>
>> Generally I can get all my answers from the Windows 2000 Scripting guide.
>> However, on the database part, they use a DSN which I do not want to use. 
>> I
>> edited the code to what I thought was right but yet the script does not 
>> work,
>> it just goes to the last line, so I think it is something in the 
>> connection
>> sting where it is failing. My code is as follows:
>>
>> Const adOpenStatic = 3
>> Const adLockOptimistic = 3
>> Const adUseClient = 3
>>
>> Set oConn = CreateObject("ADODB.Connection")
>> Set oRS = CreateObject("ADODB.Recordset")
>> sConn = "Provider=Microsoft.Jet.OLEDB.4.0;data
>> source=F:\Inventory\DataBase\Test_Inventory.mdb"
>> oConn.Open sConn
>> oRS.CursorLocation = adUseClient
>>
>> oRS.Open "SELECT * FROM INVENTORY", oConn, adOpenStatic, adLockOptimistic
>>
>> ' Loop through the recordset and determine if there is already a record. 
>> If
>> so exit script,
>> ' otherwise add new record
>> Do While NOT oRS.EOF
>> If sMAC = oRS("MAC_ADDY") Then
>>  oRS.Close
>>  oCOnn.Close
>>  WScript.Quit
>> Else
>> MsgBox sUserName & vbCrLf & sManufacturer & " " & sModel & vbCrLf &
>> sSerialNumber & vbCrLf & sIP & vbCrLf & sMAC & vbCrLf & sDomain 'Used 
>> only
>> for testing
>>  oRS.AddNew
>>  oRS("SHORT") = sUserName
>>  oRS("PC_MODEL") = sModel
>>  oRS("PC_MAKE") = sManufacturer
>>  oRS("SERVICE_TAG") = sSerialNumber
>>  oRS("IP_ADDY") = sIP
>>  oRS("MAC_ADDY") = sMAC
>>  oRS("DOMAIN") = sDomain
>>  oRS.Update
>> End If
>> Loop
>>
>> oRS.Close
>> oConn.Close
>>
>> MsgBox "Script Complete"
>>
>> I know it is not connecting to the database because 1. it remains empty, 
>> and
>> 2. the msgbox within the If statement should popup, which it doesn't. Any
>> help is appriciated.
>
> I use a connection string similar to:
> ===============
> strPath = "F:\Inventory\DataBase"
> strDB = "Test_Inventory.mdb"
>
> strConnect = "DRIVER=Microsoft Access Driver (*.mdb);" _
>        & "FIL=MS Access;DriverId=25;DefaultDir=" _
>        & strPath & ";DBQ=" & strPath & "\" & strDB & ";"
>
> Set adoConnection = CreateObject("ADODB.Connection")
> adoConnection.ConnectionString = strConnect
> adoConnection.Open
>
> -- 
> Richard Mueller
> Microsoft MVP Scripting and ADSI
> Hilltop Lab - http://www.rlmueller.net
> -- 
>

We don't see much of the code, such as where sMAC, etc., are assigned 
values. If there is a problem in the connection string you should get an 
error message. Do not use "On Error Resume Next". It will help to have the 
error message.

Once you are connected I don't think your logic works. If the recordset oRS 
has 20 rows, and the last row has the correct value for MAC_ADDY, the Else 
will run 19 times and you will add 19 new identical rows.

-- 
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

date: Tue, 22 Jan 2008 21:38:26 -0600 author: Richard Mueller [MVP]

Re: Connect to access database

"Phyxious"  wrote in message
news:5A7B90DE-E099-40E2-BE10-3F8A7788B0C9@microsoft.com...
> Generally I can get all my answers from the Windows 2000 Scripting guide.
> However, on the database part, they use a DSN which I do not want to use.
I
> edited the code to what I thought was right but yet the script does not
work,
> it just goes to the last line, so I think it is something in the
connection
> sting where it is failing. My code is as follows:
>
> Const adOpenStatic = 3
> Const adLockOptimistic = 3
> Const adUseClient = 3
>
> Set oConn = CreateObject("ADODB.Connection")
> Set oRS = CreateObject("ADODB.Recordset")
> sConn = "Provider=Microsoft.Jet.OLEDB.4.0;data
> source=F:\Inventory\DataBase\Test_Inventory.mdb"
> oConn.Open sConn
> oRS.CursorLocation = adUseClient
>
> oRS.Open "SELECT * FROM INVENTORY", oConn, adOpenStatic, adLockOptimistic
>
> ' Loop through the recordset and determine if there is already a record.
If
> so exit script,
> ' otherwise add new record
> Do While NOT oRS.EOF
>  If sMAC = oRS("MAC_ADDY") Then
>   oRS.Close
>   oCOnn.Close
>   WScript.Quit
>  Else
>  MsgBox sUserName & vbCrLf & sManufacturer & " " & sModel & vbCrLf &
> sSerialNumber & vbCrLf & sIP & vbCrLf & sMAC & vbCrLf & sDomain 'Used only
> for testing
>   oRS.AddNew
>   oRS("SHORT") = sUserName
>   oRS("PC_MODEL") = sModel
>   oRS("PC_MAKE") = sManufacturer
>   oRS("SERVICE_TAG") = sSerialNumber
>   oRS("IP_ADDY") = sIP
>   oRS("MAC_ADDY") = sMAC
>   oRS("DOMAIN") = sDomain
>   oRS.Update
>  End If
> Loop
>
> oRS.Close
> oConn.Close
>
> MsgBox "Script Complete"
>
> I know it is not connecting to the database because 1. it remains empty,
and
> 2. the msgbox within the If statement should popup, which it doesn't. Any
> help is appriciated.

How about just ensuring that undesired records are not selected, as in:

oRS.Open "SELECT * FROM INVENTORY WHERE MAC_ADDY <> '" _
 & sMAC & "'", oConn, adOpenStatic, adLockOptimistic


Then remove
     If sMAC = oRS("MAC_ADDY") Then
      oRS.Close
      oCOnn.Close
      WScript.Quit
     Else
and
     End If

Of course, you're also missing
    oRS.MoveNext
before
    Loop

date: Tue, 22 Jan 2008 22:20:34 -0600 author: McKirahan

Re: Connect to access database

Richard,

The section of code where sMAC and other variables are assigned do work so 
thats why I did not add the code. As of right now the database is empty, I 
changed my code for the connection to what you have listed and I still can 
not get any records to be added.

Maybe my logic is screwed up, as I am not the greatest programer. I have 
never user On Error Resume Next in my scripts, so there is no worry there and 
I do not ever receive an error message, so I could be connecting to the 
database but it just isn't adding the record(s).

I only think it is not connecting because seeing that there is no records 
then within in the DO Loop it should goto the else statement, and the 
messagebox should pop up, which it doesn't, it just goes to the end of the 
script.

So its either not connecting or my logic is screwed up.

date: Wed, 23 Jan 2008 09:30:01 -0800 author: Phyxious

Re: Connect to access database

"Phyxious"  wrote in message
news:7D3B95B5-299D-4343-8A8E-5C49731E1718@microsoft.com...
> Richard,
>
> The section of code where sMAC and other variables are assigned do work so
> thats why I did not add the code. As of right now the database is empty, I
> changed my code for the connection to what you have listed and I still can
> not get any records to be added.
>
> Maybe my logic is screwed up, as I am not the greatest programer. I have
> never user On Error Resume Next in my scripts, so there is no worry there
and
> I do not ever receive an error message, so I could be connecting to the
> database but it just isn't adding the record(s).
>
> I only think it is not connecting because seeing that there is no records
> then within in the DO Loop it should goto the else statement, and the
> messagebox should pop up, which it doesn't, it just goes to the end of the
> script.
>
> So its either not connecting or my logic is screwed up.

If there are one or mores records in the databse then try to display then
via:
    WScript.Echo "MAC_ADDY = " & oRS("MAC_ADDY")
immediately after to "Do" statement.


With
    "SELECT * FROM INVENTORY"
you are selecting all records from the INVENTORY table.

A Do Loop (with a "oRS.MoveNext"! which is missing)
is used to process >1 records; if there is only one record then change:
    Do While NOT oRS.EOF
    Loop
 to:
    If Not oRS.EOF Then
    End If

Me thinks your problem may be with the recordset as you
are reading and (attempting to) write to a table in one pass.

Theselinks may help:
http://www.w3schools.com/ado/ado_ref_recordset.asp
http://www.wpunj.edu/irt/courses/csrv/cs440rv/ado.htm

date: Wed, 23 Jan 2008 16:20:49 -0600 author: McKirahan

Re: Connect to access database

Ok I got everything to work as I wanted, just need more testing to verify 
everything is working. I do appriciate all your help.

One more thing is I do know MS Access is not built for this but is it 
possible to connect to the database on a remote server? I really do not care 
about performance issues.

date: Thu, 24 Jan 2008 12:01:01 -0800 author: Phyxious

Re: Connect to access database

"Phyxious"  wrote in message
news:591B7962-C613-430E-B378-B0A14FD0854C@microsoft.com...
> Ok I got everything to work as I wanted, just need more testing to verify
> everything is working. I do appriciate all your help.
>
> One more thing is I do know MS Access is not built for this but is it
> possible to connect to the database on a remote server? I really do not
care
> about performance issues.

Start a new thread with your question so others will respond.

date: Thu, 24 Jan 2008 14:12:17 -0600 author: McKirahan