PDA

View Full Version : Excel VBA - How to Create Read Write Edit Del by ADO - Jet.OLEDB - Access - MDB



finsel
12-31-2010, 04:38 AM
Hi guys!

I need your help... I have some problems in use mdb in vba excel...
(look, I'm using ADO, because this was the unique code that worked here... So, I can use anything else if work.)

So, what I need and what I have:
CSV_1 = ServerName, ServerValue
CSV_2 = ServerName, ServerType
CSV_3 = ServerType, ServerNewValue

Then I need to:
1. create Access mdb
2. Fill the mdb with CSV_1 data + 2 columns empty
3. Read CSV_2 and put the ServerType in the recordset with same ServerName
4. Read CSV_3 and put the ServerNewValue in the recordser with same ServerType

The record set should have: :doh:
ServerName / ServerValue / ServerType / ServerNewValue
( I have some others columns, but theses can explain what I need exactly)
( in the script the fields have name like HC_sssss)

What I made: :yes
Created a MDB
Read MDB ( I can't test as well)

What is pending: :dunno
Write data in RecordSet
Find and update a RecordSet

Pls help! :arrowl:


___VBA Excel ___

Sub Create_DB_and_Table_Using_ADOX()
Dim oDB As ADOX.Catalog
Dim sDBPAth As String
Dim sConStr As String
Dim oCn As ADODB.Connection
Dim oCM As ADODB.Command
' ------------------------
' Set the Path and Connection String
' ------------------------
sDBPAth = sFolder & "HCTable.mdb"
sConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPAth & ";"
' ------------------------
' Create New ADOX Object
' ------------------------
Set oDB = New ADOX.Catalog
oDB.Create sConStr
Set oCn = New ADODB.Connection
oCn.ConnectionString = sConStr
oCn.Open
Set oCM = New ADODB.Command
oCM.ActiveConnection = oCn
'create table named: HC
oCM.CommandText = "Create Table Table_HC (" & "[HC_Client] Text(64), " & "[Type_OU] Text(150), " & "[HC_Description] Text(150), " & "[HC_Value] Text(64), " & "[Exc_Description] Text(150), " & "[Exc_Value] Text(64) " & ")"
oCM.Execute

' ------------------------
' Release / Destroy Objects
' ------------------------
If Not oCM Is Nothing Then Set oCM = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing
If Not oDB Is Nothing Then Set oDB = Nothing

' ------------------------
' Error Handling
' ------------------------
Err_Handler:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub

'___
'___
'___

Sub Access_Data()
'Requires reference to Microsoft ActiveX Data Objects xx Library

Dim Cn As ADODB.Connection, rs As ADODB.Recordset
Dim MyConn, sSQL As String

Dim Rw As Long, Col As Long, c As Long
Dim MyField, Location As Range

'Set destination
Set Location = [B2]
'Set source
MyConn = "C:\Documents and Settings\Administrator\My Documents\TEST\HCTable.mdb"
'Create query
sSQL = "SELECT Table_HC.HC_Client, Table_HC.Type_OU FROM Table_HC;"

'Create RecordSet
Set Cn = New ADODB.Connection
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
Set rs = .Execute(sSQL)
End With

'Write RecordSet to results area
Rw = Location.Row
Col = Location.Column
c = Col
Do Until rs.EOF
For Each MyField In rs.Fields
Cells(Rw, c) = MyField
c = c + 1
Next MyField
rs.MoveNext
Rw = Rw + 1
c = Col
Loop
Set Location = Nothing
Set Cn = Nothing
End Sub

___

Thank you!
Best regards, Frank

Bob Phillips
12-31-2010, 07:10 AM
Can u post the CSV files to test with?

finsel
12-31-2010, 07:51 AM
attached my vba and the 3 csv samples...

finsel
01-03-2011, 04:00 AM
Please Help! I have just few days to complete this... :/
tks.