• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Update Access via Excelsheet

  • Onderwerp starter Onderwerp starter AatB
  • Startdatum Startdatum
Status
Niet open voor verdere reacties.
AatB

AatB

Gebruiker
Lid geworden
15 dec 2007
Berichten
257
Geacht Forum,

Ik ben al wat verder met het gebruiken van excel als frontend voor access.
Ik wil alleen maar updaten en dus niet toevoegen of deleten.

Echter de code in excel die ik gebruik werk nog niet goed.

Ik vind alleen het eerste record in test.mdb....

Kunnen jullie mij helpen?

mvg,

Aat


Code: 
Sub UpdateTest()
    
    Dim dbTest          As ADODB.Connection
    Dim rs              As ADODB.Recordset
    Dim exceldata       As Range
    
    Dim strCust_ID      As String
    Dim strCust_Name    As String
    Dim strSQL          As String
            
    Set dbTest = New ADODB.Connection
    
    dbTest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    Workbooks(1).Path & "\test.mdb"
    
    dbTest.Open
    Set rs = New ADODB.Recordset
    Set exceldata = Worksheets("Blad1").Range("A2")
    
    'Add data Excelsheet
    Do While exceldata <> ""
        If exceldata.Offset(0, 0) <> exceldata.Offset(-1, 0) Then
            strCust_ID = exceldata
            strCust_Name = exceldata.Offset(0, 1)
            strSQL = "SELECT * FROM tblTest 'WHERE Cust_ID=" & strCust_ID
            With rs
                .Open strSQL, dbTest, adOpenStatic, adLockOptimistic, adCmdText
                    If strCust_ID <> .Fields("Cust_ID") Then GoTo nkst
                .Fields("Cust_ID") = strCust_ID
                .Fields("Cust_Name") = strCust_Name
                .Update
nkst:
               .Close
            End With
        End If
        Set exceldata = exceldata.Offset(1, 0)
    Loop
    
    dbTest.Close
    Set dbTest = Nothing
End Sub
 

Bijlagen

Is dit niet simpeler ?

Code: 
Sub Macro15()
  With Sheets("Blad1").QueryTables.Add("ODBC;DSN=MS Access-database;DBQ=E:\test.mdb", Sheets("Blad1").Range("A2"))
    .CommandText = "SELECT tbltest.Cust_ID  FROM `E:\test`.tbltest"
    .Refresh False
  End With
End Sub
 
snb zei:
Is dit niet simpeler ?

Code: 
Sub Macro15()
  With Sheets("Blad1").QueryTables.Add("ODBC;DSN=MS Access-database;DBQ=E:\test.mdb", Sheets("Blad1").Range("A2"))
    .CommandText = "SELECT tbltest.Cust_ID  FROM `E:\test`.tbltest"
    .Refresh False
  End With
End Sub
Klik om te vergroten...

Hoi snb,

Met jou code haal je de data van Access naar Excel.
Wel mooi simpel gehouden.

Het gaat er mij nu om dat ik deze data in Excel aanpas en dat deze aanpassing ook in Access plaatsvindt.

mvg,

Aat
 
Beste Aatb,

Ik ben geen Access-expert, maar moet je in jouw geval niet vanuit Access werken?
Ik weet dat je vanuit een database in Access een verbinding kunt leggen met een Excelbestand, maar heb dat nog nooit gedaan. Probeer dit eens?

Groeten,
Richard
 
Je kunt met een query een selectie verwijderen uit de Access-database en vervolgens het opgeslagen Excel-bestand importeren.

Voortbordurend op jouw code:
Een wat omslachtiger methode lijkt me deze
Code: 
Sub koppel()
  With Sheets("[COLOR="Teal"]Blad1[/COLOR]").QueryTables.Add("ODBC;DSN=MS Access-database;DBQ=[COLOR="teal"]E:\fiets.mdb[/COLOR]", Sheets("[COLOR="teal"]Blad1[/COLOR]").Range("[COLOR="teal"]A2[/COLOR]"))
    .CommandText = "SELECT [COLOR="teal"]tabel1.lidnummer[/COLOR]  FROM [COLOR="teal"]`E:\fiets`.tabel1[/COLOR]"
    .Refresh False
  End With
End Sub
Code: 
Sub update()
    With ActiveSheet.QueryTables(1)
        c1 = Replace(Join(Filter(Split(.Connection, ";"), "DBQ="), ""), "DBQ=", "")
        c2 = Replace(Join(Filter(Split(.CommandText & "*", "."), "*"), ""), "*", "")
    End With
    With GetObject(c1).currentdb.openrecordset(c2)
        .Index = "[COLOR="teal"]lidnummer[/COLOR]"
        For Each cl In Columns(1).SpecialCells(xlCellTypeConstants)
            If cl <> [COLOR="teal"]"lidnummer"[/COLOR] Then
                .Seek "=", cl
                .Edit
                [COLOR="teal"]!adres [/COLOR]= "test"
                .Update
            End If
        Next
    End With
End Sub
 
Laatst bewerkt:
snb zei:
Je kunt met een query een selectie verwijderen uit de Access-database en vervolgens het opgeslagen Excel-bestand importeren.

Voortbordurend op jouw code:
Een wat omslachtiger methode lijkt me deze
Code: 
Sub koppel()
  With Sheets("Blad1").QueryTables.Add("ODBC;DSN=MS Access-database;DBQ=E:\fiets.mdb", Sheets("Blad1").Range("A2"))
    .CommandText = "SELECT tabel1.lidnummer  FROM `E:\fiets`.tabel1"
    .Refresh False
  End With
End Sub
Code: 
Sub update()
    With ActiveSheet.QueryTables(1)
        c1 = Replace(Join(Filter(Split(.Connection, ";"), "DBQ="), ""), "DBQ=", "")
        c2 = Replace(Join(Filter(Split(.CommandText & "*", "."), "*"), ""), "*", "")
    End With
    With GetObject(c1).currentdb.openrecordset(c2)
        .Index = "lidnummer"
        For Each cl In Columns(1).SpecialCells(xlCellTypeConstants)
            If cl <> "lidnummer" Then
                .Seek "=", cl
                .Edit
                !adres = "test"
                .Update
            End If
        Next
    End With
End Sub
Klik om te vergroten...



Hi SNB,

ik heb jouw update code geprobeerd, maar krijg een foutmelding op;

Code: 
With GetObject(c1).currentdb.openrecordset(c2)

File name or class name not found during Automation operation.

Het lijkt er op of (de waarde van c2) er tussen de databasenaam en de tabelnaam geen scheidingsteken staat.

Doe ik wat fout?

mvg,

Aat
 
richard1970 zei:
Beste Aatb,

Ik ben geen Access-expert, maar moet je in jouw geval niet vanuit Access werken?
Ik weet dat je vanuit een database in Access een verbinding kunt leggen met een Excelbestand, maar heb dat nog nooit gedaan. Probeer dit eens?

Groeten,
Richard
Klik om te vergroten...

Hoi Richard,

dat doe ik nu ook (40 tabellen in de database >> 40 spreadsheets). Ik wil dit juist anders maken. 1 tabel in de database met een filter naar 40 verschillende spreadsheets, zodat vanuit deze 40 spreadsheets 1 tabel onderhouden kan worden.

mvg,

Aat
 
Mijn code gaat uit van een Access-bestand fiets.mdb, met een tabel 'tabel1', met velden lidnummer en adres.
Zo'n bestand heb jij niet, dus pas de variabele zaken aan aan jouw situatie. Ik zal die nog even in mijn code markeren.

Geef vervolgens aan welke waarden de variabelen c1 en c2 aannemen; dan is het euvel sneller gevonden.

PS. Niet-citeren vind ik wel zo handig lezen.
 
Laatst bewerkt:
Hoi SNB,

Ik heb een Testab.mdb in de directory TEST staan met een tblTest.
De velden zijn Cust_ID en Cust_Name

Ik heb jouw code aangepast:

Code: 
Sub update()
    With ActiveSheet.QueryTables(1)
        c1 = Replace(Join(Filter(Split(.Connection, ";"), "DBQ="), ""), "DBQ=", "")
        c2 = Replace(Join(Filter(Split(.CommandText & "*", "."), "*"), ""), "*", "")
  
    End With
    With GetObject(c1).currentdb.openrecordset(c2)
        .Index = "Cust_ID"
        For Each cl In Columns(1).SpecialCells(xlCellTypeConstants)
            If cl <> "Cust_ID" Then
                .Seek "=", cl
                .Edit
                !Cust_Name = "test"
                .update
            End If
        Next
    End With
End Sub

De waarde van c1 en c2 is;

c1 = C:\Documents and Settings\AatB\Desktop\Test\Testab.mdb
c2= tblTest tblTest

Ik krijg een foutmelding:

Run time error '-2147467259 (80004005)

Automation error
Unspecifed error

mvg,

Aat
 
De waarde van c2 moet zijn: tblTest

dat kun je voor elkaar krijgen door

I. in de macro van de koppeling de tekst van Commandtext te wijzigen
.CommandText = "SELECT tblTest.Cust_ID FROM `E:\TEST\Testab`.tblTest"

Of

II. door in de macro update de volgende regel te wijzigen:
Code: 
c2 = Replace(Join(Filter(Split(.CommandText & "*", "."), "*"), ""), "*", "")
in
Code: 
c2 = Replace(Join(Filter([COLOR="Blue"]Split(.CommandText & "*"), [/COLOR]"*"), ""), "*", "")
 
Hoi SNB,

helaas krijg ik nog steeds dezelfde foutmelding.....

mvg,

Aat
 
Hoi SNB,

ik attach even de database en de spreadsheet van mij.....
Misschien zie jij direct de oplossing....

Ik wacht af...

mvg,

Aat
 

Bijlagen

Als je zorgt voor een excel en accesbestand versie 2003 kan ik ernaar kijken.
Nu zit er slechts 1 bestand in de zip; het ging toch over de koppeling ?
 
Hoi SNB,

volgens mij zit er een test.mdb en een test.xls in het zip file.....
Ik heb nog even gekeken....

mvg,

Aat
 
Ik ook, doch ontdek noch een Excel- noch een Accessbestand.
 
Helaas, ik zie slechts 1 extensieloos bestand in jouw gezipte bestand.
Zet het excelbestand ongezipt en het mdb-bestand gezipt hier neer svp.
 
Zo lukt het wel denk ik want met Zip-files tegenwoordig niets dan problemen hier

Mvg

Rudi
 

Bijlagen

Laatst bewerkt:
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan