VBA Create Table Column of SQL Server for Binary-type Data

[zxcvbc]

Hi All,

Below is the code I use, currently facing the problem to declare the datatype of 'Data' as Binary-type for file upload.

Sub ImportToDatabase()
'Import Data to SQL Server
    Dim conn As New ADODB.Connection
    Dim adoCmd  As Object
    Dim iRowNo As Integer
    Dim Notification, Material, FileName, ContentType As String
   
    Dim strConn As String
    Set adoCon = CreateObject("ADODB.Connection")
    Set adoCmd = New ADODB.Command
                
'Open a connection to SQL Server
        strConn = "Provider=SQLOLEDB;Data Source=SERVER NAME;Initial Catalog=test;Integrated Security=SSPI;"
        
        With conn
            .Open strConn
            .Execute " DROP TABLE [Testing];"

            
            .Execute "CREATE TABLE [Testing](Notification varchar(8000) not null," & "[Material] varchar(8000) not null," & "FileName varchar (8000) null," & "ContentType varchar (8000) null)"
        End With
        'Skip the header row
        iRowNo = 2
            
        'Loop until empty cell in notification
        Do Until Cells(iRowNo, 1) = ""
            Notification = Cells(iRowNo, 1)
            Material = Cells(iRowNo, 2)
            FileName = Cells(iRowNo, 3)
            ContentType = Cells(iRowNo, 4)
            
                    
    'Application.RefreshDatabaseWindow
       'Generate and execute sql statement to import the excel rows to SQL Server table
    
            conn.Execute "insert into [Testing] ( Notification, Material, FileName, ContentType)" & _
            "values ('" & Notification & "', '" & Material & "','" & FileName & "', '" & ContentType & "')"
               
            iRowNo = iRowNo + 1
        Loop
End Sub

 

[Norie]

Is 'Data' the name of the column you want to create?

 

[zxcvbc]

'Data' is the name which I want to create as binary-type. Thank you.

 

[Norie]

Can't you add something like this to your CREATE TABLE statement

[Data] varbinary

So the full statement would look something like this.

CREATE TABLE [Testing]
(Notification varchar(8000) not null,
[Material] varchar(8000) not null,
FileName varchar (8000) null,
ContentType varchar (8000) null
[Data] varbinary)

PS What type of files are you uploading?

 

[zxcvbc]

I declare like [Data] varbinary but there is an error asking to convert varchar to varbinary. I am trying to upload Excel files. Thank you.

 

[Norie]

When do you get the error?

Is it when you are creating the table or when you are trying to upload data?

If its the latter how are you trying to upload the data?