Excel VBA - How to Create Read Write Edit Del by ADO - Jet.OLEDB - Access - MDB
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:
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:
Created a MDB
Read MDB ( I can't test as well)
What is pending:
Write data in RecordSet
Find and update a RecordSet
Pls help!
___VBA Excel ___
[VBA]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[/VBA]
'___
'___
'___
[VBA]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[/VBA]
___
Thank you!
Best regards, Frank
Reply With Quote
