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

zxcvbc

New Member
Joined
Feb 20, 2019
Messages
6
Hi All,

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

Code:
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Is 'Data' the name of the column you want to create?
 

zxcvbc

New Member
Joined
Feb 20, 2019
Messages
6
'Data' is the name which I want to create as binary-type. Thank you.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 20, 2019
Messages
6
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,907
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top