Archive for category Microsoft Access

Copy Data from Excel to SQL Server (or any DBMS) Script to generate Insert Statements in Excel

Unfortunately sometimes copy / paste from Excel into SQL server doesn’t work. Since moving data from Excel into SQL server (or another DBMS) can sometimes prove trickier than it should be I’ve created this handy script to help with just that. To get it to work:
1) Paste it into your excel project as a macro.
2) Select the sheet you want to copy
3) The first row of your sheet must contain the column names of the table to be inserted into.
4) Update the macro to use the correct table (look for TABLE_NAME_HERE)

That’s it! Put your cursor in the method and press F5. Upon completion you’ll get a message box with “complete” in it. Once the file is generated copy the contents of c:\temp\insert.txt into Management Studio / Toad / etc and run it.

Sub GenerateInsertRecords()
'Code thanks to
'Feel free to use, change and re-distribute the code you find on my site,
'all I ask is you leave these these comments intact!
    Dim i As Integer, ii As Double, iColumn as integer
    Dim str As String, strInsert As String, strComplete As String
    str = ""
    str = "insert into TABLE_NAME_HERE ("
    For i = 1 To 999
        If Len(Cells(1, i)) < 1 Then Exit For
        str = str & Cells(1, i) & ","
    Next i
    str = Left(str, Len(str) - 1)
    str = str & ") values ("
    Dim oConvert As Variant
    strInsert = str
    strComplete = ""
    For ii = 2 To 65000
        If Len(Cells(ii, 1)) < 1 Then Exit For
        strComplete = strComplete & strInsert
        str = ""
        For iColumn = 1 To i - 1
            oConvert = getInput(Cells(ii, i))
            str = str & oConvert & ","
        Next iColumn
        str = Left(str, Len(str) - 1)
        str = str & ")"
        strComplete = strComplete & str & vbNewLine
    Next ii
    Dim oFS As Scripting.FileSystemObject
    Set oFS = CreateObject("Scripting.FileSystemObject")
    Dim oText As Scripting.TextStream
    Set oText = oFS.OpenTextFile("c:\temp\insert.txt", ForWriting, True)
    oText.Write (strComplete)
    MsgBox ("complete")
End Sub

Function getInput(theInput As Variant) As Variant
    Dim retval As Variant
    If IsNull(theInput) Then
        retval = "Null"
    ElseIf UCase(theInput) = "NULL" Then
        retval = "Null"
    ElseIf IsNumeric(theInput) Then
        retval = theInput
        retval = "'" & theInput & "'"
    End If
    getInput = retval
End Function


Calculating PI, ASin, ACos in VB (VBScript, Excel, Word, Access)

I was recently surprised when I found out a number of numerical functions don’t exist in Access. Granted I believe they do exist in Excel, and probably word, but if you’re doing any coding you may find this useful anyway. Here is the code to accomplish all three:

'Code thanks to
'Feel free to use, change and re-distribute the code you find on my site,
'all I ask is you leave these these comments intact!
Public Function PI() As Double
    PI = 4 * Atn(1)
End Function

Public Function ASin(val As Double) As Double
    ASin = 2 * Atn(val / (1 + Sqr(1 - (val * val))))
End Function

Public Function ACos(val As Double) As Double
    ACos = PI / 2 - ASin(val)
End Function

If you’re wondering, Atn() IS built into Access, so these functions should work as is, they do for me!

Leave a comment

Parameterized SQL / Transactional SQL in VB / Access

I have got to get out of the legacy world and start posting something more current. I’m currently working for a client that has an enormous amount of data and code invested in MS Access and I have the go ahead to convert it to .Net… But. But I have to continue to support and debug the current system at the same time. It will be a while, but I’ll have this thing in tip top shape yet.

So, if you’re a poor sap like me and have to maintain some legacy code you may find this tidbit useful. How to write parameterized SQL in VB / Access code and I added transaction support to boot.

Private Sub DoBatch(ByRef rs As ADODB.Recordset)
Dim cn As ADODB.Connection
Dim cmd As New ADODB.Command

Set cn = CurrentProject.AccessConnection
Call cn.BeginTrans

Dim sCMD As String
sCMD = “update t_TableName set Var1=@inVar1, Var2=@inVar2, Var3=@inVar3, ” & _
“Var4=@inVar4 Where Var5=@inVar5”
Do While Not rs.EOF
With cmd
.ActiveConnection = cn
.CommandText = sCMD
Call .Parameters.Append(CreateParameter(“@inVar1”, nz(rs.Fields(“Var1”), 0), 4, adNumeric))
Call .Parameters.Append(CreateParameter(“@inVar2”, nz(rs.Fields(“Var2”), 0), 4, adNumeric))
Call .Parameters.Append(CreateParameter(“@inVar3”, nz(rs.Fields(“Var3”), 0), 4, adNumeric))
Call .Parameters.Append(CreateParameter(“@inVar4”, nz(rs.Fields(“Var4”), 0), 50, adVarChar))
Call .Parameters.Append(CreateParameter(“@inVar5”, nz(rs.Fields(“Var5”), 0), 4, adNumeric))
Call .Execute()
End With
Call rs.MoveNext

Call cn.CommitTrans

End Sub

Private Function CreateParameter(ByVal Name As String, ByVal value As Variant, ByVal Size As ADO_LONGPTR, ByVal theType As ADODB.DataTypeEnum, Optional ByVal direction As ParameterDirectionEnum = dbParamInput) As ADODB.Parameter
Dim tempP As New ADODB.Parameter
tempP.Name = Name
tempP.value = value
tempP.direction = adParamInput
tempP.Size = Size
tempP.Type = theType
Set CreateParameter = tempP
End Function

Leave a comment

Disconnected RecordSet VB / Access with ADO

Code to run a disconected recordset (which by the way will allow the .RecordCount property to be calculated). This is useful mainly for reads of data where you don’t need to lock the recordset for any reason. Before we migrated to .Net we used this in our enterprise web applications all the time.

Public Sub GetData()
On Error GoTo err_des
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = CurrentProject.AccessConnection ‘Here we’re using Microsoft Access, you could create your own connection in much the same way.

rs.CursorLocation = adUseClient
Call rs.Open(“q_GetSomeData”, cn, adOpenStatic, adLockBatchOptimistic)
Set rs.ActiveConnection = Nothing

‘do something with rs here.
Exit Sub
MsgBox “oops: ” & Err.Description
End Sub

Leave a comment

How to decode a blob datatype to string in VB / Access

Private Function Decode(ByRef rc As ADODB.Recordset) As String
Dim myStream As New ADODB.Stream
myStream.TYPE = adTypeBinary
Call myStream.Write(rc.Fields(“theBlob”))
myStream.Position = 0

Dim sResults As String
sResults = “”

Do While Not myStream.EOS
sResults = sResults & Chr(AscB(myStream.Read(1)))

Decode= sResults & vbNewLine
End Function

This code had me scratching my head at first. The Stream.Write() method writes into the stream from the record field, not to some standard output like a file IO object would. Also be sure you’re referencing one of the newer versions of Microsoft ActiveX Data Objects X.X Library. I’m referencing 2.8 which works like a charm. The project I modified originally referenced 2.0 which didn’t even have a .Stream class in ADODB.

Leave a comment

How to use ADO in MS Access / Visual Basic

Just a quick example of data access code that uses ADO to get a recordset. Typically this type of code ports well from Access to VB and vice versa. It also nearly ports to script files, remove the variable types and you’re good to script.

Private Sub SetSomeData()
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim param As ADODB.Parameter
Set param = New ADODB.Parameter
param.NAME = “theColumn_Id”
param.value = “Some Value”
param.TYPE = adVarChar
param.Size = 40
param.Direction = adParamInput

Set cn = CurrentProject.AccessConnection

Set cmd = New ADODB.Command
With cmd
Call .Parameters.Append(param)
.ActiveConnection = cn
.CommandText = “q_GetSomeData” ‘uses a MS Access query
.CommandType = adCmdTable
Set rs = .Execute()
End With

If rs.RecordCount > 0 Then
tSomeData = GetSomeDataFromTheRecordset(rs)
tSomeData = “”
End If
Call rs.Close
End Sub

This is what the Access query looks like:
PARAMETERS theColumn_ID Text ( 255 );
SELECT Column1, Column2
FROM my_Table
WHERE Column_ID = theColumn_ID

Leave a comment

In MS Access you can’t update a table from a read only query

It’s possible that this is caused by the archaic version of Access I’m using (2002) but this query, which would normally work doesn’t if you join it with a read only query. This would make sense if I was trying to update the query values, but all I want to do is update the table from the query. Apparently access can’t handle that.

SET Bot.MFG = [Big].[MFG];

A good way to create a read only query:
select left(colum,7) as FirstSevenChars from SomeTable

I’m working with a bunch of non normalized tables and they sometimes use sql like this to join tables. So part of a unique field is the key to another table. Needless to say it’s a terrible mess. Really, normalization is a good thing: Wiki Normalization.