Send data to Server ADODB

[johnny_p]

Hi all

I am looking to connect to a server and send data from a sheet within Excel.

The range I am looking to send is from "B15:AO64" but only if there is data by checking rows upwards.

This is the sample code I have been provided.

Please Login or Register  to view this content.

Could you please help by filling in the blanks?

Thanks

[Norie]

How many fields are in the table you are appending to and what data type are they?

Do all 40 columns in correspond to a field in the table?

[johnny_p]

Thanks for Looking.

The Columns B:AO all match a Field in the table.

B = “Name”, C = “REF”, Etc.

The Data types are a mixture. Would it be best to export them as a String?

Does this help?

Thanks again

[Norie]

The data types of the fields will affect the SQL for the insert query.

For example the values for string/text fields will need to be in single quotes.

Also, you would need to have a separate insert query for each row of data you want to append to the table.

So this isn't going to be a particuarly straightforward task.

To simplify things you might want to look into saving the data to a csv file and then doing a bulk insert of the csv to the table.

[johnny_p]

Can a CSV file be used without the user knowing about it. For example a temp CSV file created and then closed?

No CSV would be Ideal.

If you get me started could I not just add the remaining 40 lines?

Thanks again for taking the time to help out

[Norie]

It's pretty straightforward to save data to a temporary CSV file which could be then deleted after the data has been appended.

If you have 40 fields and 40 values you are going to end up with a pretty long, complicated SQL statement and to even get started on helping with that I need see data, know the field names/data types etc.

PS If this is going to be a regular thing you might want to think about setting a query up in the database for the bulk insert.

That query could then be executed from your code whenever it was needed.

[johnny_p]

There will only be 40 fields in total with a mix of Str/Int.

The tool will be used by multiple users independently and once they have completed it the tool then saves the data to the server and closes down without saving any changes to the file itself.

[Norie]

That's still a lot of fields.

Here's a very simplified version of how the SQL might look.

INSERT INTO VBA_SAMPLE (Field1,Field2,Field3,Field4,Field5,Field6,Field7,Field8,Field9,Field10,Field11,Field12,Field13,Field14,Field15,Field16,Field17,Field18,Field19,Field20,Field21,Field22,Field23,Field24,Field25,Field26,Field27,Field28,Field29,Field30,Field31,Field32,Field33,Field34,Field35,Field36,Field37,Field38,Field39,Field40) VALUES(Value1,Value2,Value3,Value4,Value5,Value6,Value7,Value8,Value9,Value10,Value11,Value12,Value13,Value14,Value15,Value16,Value17,Value18,Value19,Value20,Value21,Value22,Value23,Value24,Value25,Value26,Value27,Value28,Value29,Value30,Value31,Value32,Value33,Value34,Value35,Value36,Value37,Value38,Value39,Value40)


I really would recommend you look into the bulk insert.

You can easily export the data to be uploaded to a CSV file, upload it and then delete the file, all using code.

[johnny_p]

Would the bulk insert work faster?


Sent from my iPhone using Tapatalk

[Norie]

I really don't know if it would be faster but I do know it'll probably be a bit more straightforward.

For example this is an example of the SQL for a bulk insert from a csv.

BULK INSERT TableName
FROM 'C:\SomeDirectory\SomeFile.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO