SQL Server bruker par av enkle anførselstegn for å identifisere start og enden av en streng. Å sette inn 'Mrs Brown's Boys' i en databasetabell vil mislykkes siden de tre enkle anførselstegnene antyder to strenger, hvorav den ene er ufullstendig. Et escape-tegn kreves for apostrophe etter Brown. Denne artikkelen utforsker bruken av en tilpasset VBA-funksjon for å løse denne anomalien.
Denne artikkelen forutsetter at leseren har utviklerbåndet vist og er kjent med VBA Editor. Hvis ikke, vennligst Google "Excel Developer Tab" eller "Excel Code Window".
Begrepet "database" her gjelder for "industriell styrke" databaser som SQL Server og Oracle.
Du finner et eksempel på arbeidsboken brukt i denne øvelsen her..
SQL-strengen
Inkludering av apostrophes (eller enkle anførselstegn) inne i en SQL-setning gir følgende feil returnert fra databasebehandlingen (for navnet O'Dowd i dette tilfellet):
En escape-karakter er nødvendig, som er en dobbel apostrophe i stedet for en enkelt. Dermed er O”Dowd akseptabelt for databasen. O'Dowd er ikke det.
Funksjonen
Hvor fangstfelt kan tenkes å inneholde en apostrophe, en egendefinert funksjon kan bygges for å avfyres før oppdatering, og erstatte det enkle anførselstegn med et dobbelt.
- Åpne en ny arbeidsbok;
- Navngi det første arket "Oppdater" og fullfør som følger, bruk ditt eget databasenavn osv. Disse feltene vil bli brukt til å bygge en tilkoblingsstreng til SQL Server.
- Åpne kodevinduet og sett inn en modul. Bruk menyelementene >Verktøy >Referanser for å referere til ADO librarut.
Kopier koden nedenfor inn i modulen. Denne kobles til databasen.
Public connDB As New ADODB.Connection Public rs As New ADODB.Recordset Public strSQL As String Public strCriteria As String Sub ConnectDatabase() If connDB.State = 1 Then connDB.Close On Error GoTo ErrConnect Dim strServer, strDBase, strUser, strPWD As String strServer = Sheets("Update").Range("B2") strDBase = Sheets("Update").Range("B3") strUser = Sheets("Update").Range("B4") strPWD = Sheets(" Update").Range("B5") Hvis strPWD > "" Da strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPWD & ";Tidsavbrudd for tilkobling=30;" Else strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & ";Trusted_Connection=yes;DATABASE=" & strDBase 'Windows-autentiseringsslutt If connDB.ConnectionTimeout = 30 connDB.Open strConnectionstring Exit Sub ErrConnect: MsgBox Err.Description End Sub
- Legg til funksjonen i modulen:
Funksjon fRemoveApostrophe(strOrd som streng) Dim n som heltall Dim x som heltall x = 0 For n = 0 Til 100 x = InStr(x + 2, strWord, "'") 'Finn posisjonen til apostrophes If x = 0 Then Exit For If x > 0 Then strWord = Left(strWord, x - 1) & Chr(39) & Chr(39) & Right(strWord, Len(strWord) - (x)) End If Next n fRemoveApostrophe = strWord End Function
- Ignorer funksjonen.
Sub IgnoreFunction() Call ConnectDatabase strCriteria = Sheets("Update").Range("B10") strSQL = "Insert into tblCrewMember (LastName)-verdier ('" & strCriteria & "')" MsgBox strSQL & ". Denne SQL-oppføringen vil mislykkes; merk de tre apostrophes." Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
- Bruk funksjonen
Sub UseFunction() Call ConnectDatabase strCriteria = Sheets("Update").Range("B15") strCriteria = fRemoveApostrophe(strCriteria) strSQL = "Sett inn i tblCrewMember (Etternavn) verdier ('" & strCriteria & "')" MsgBox strSQL & ". Denne SQL-oppføringen vil lykkes, og vises i datatabellen som O'Dowd." Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
- Fullfør Oppdater arbeidsark som følger, starting ved celle A8:
- Tilordne knappene til makroer Ignorer funksjon og Bruk funksjon henholdsvis
Resultatene
En meldingsboks vil vise resultatene; ingen database er fysisk oppdatert i denne øvelsen, men hvis du ønsker å gjøre det, sørg for at feltnavnene er kompatible med databasen din, og legg til bruk VBA-setningen conndb.execute(strSQL)
Gjenopprette fra Excel-krasj
Excel er utsatt for å krasje når datamaskinen går tom for ressurser. Under skrivingen av denne øvelsen frøs Excels regneark, som ennå ikke er lagret. Kodevinduet var delvis responsivt, og tillot lukking av arbeidsboken som helhet. Som det viste seg, åpnet arbeidsboken normalt igjen med innholdet og koden komplett. Hadde tempoetrary og kildefiler blitt (altfor ofte) skadet, ville arbeidet måttet gjøres om i mangel av et verktøy for å løse xlsx skade. Det var av liten betydning i dette tilfellet, men kan være en potensiell katastrofe for større arbeidsbøker.
Forfatterintroduksjon:
Felix Hooker er en datagjenopprettingsekspert innen DataNumen, Inc., som er verdensledende innen datagjenopprettingsteknologier, inkludert gjenopprette rar og sql-programvareprodukter. For mer informasjon besøk www.datanumen. Med