Using VBA Excel to Export Data to 'JobBOSS' (Sql Server Database)

[killpaddy]

Hello,

I am very new to VBA and computer Database programming in general, so any information or guidance would be appreciated.

I have got an Excel Stocklist with flieds: Article Name, Description, QTY and Tray

I am hoping to upload just the QTY column to the 'JobBOSS' Database and update the value there using a key field such as Article Name. The programme CANNOT replace as this could affect/ delete the other fields in JobBOSS such as 'price'.

I think the JobBOSS database is Sql, though im not entirely sure what this means

I have thought about using the 'Get External Data' function in excel to begin with to retrieve the data from JobBOSS and then edit it, however i dont know if this is possible in the VBA editor.

I realise this is vauge, but any information would be useful at this stage

Thanks,

Killpaddy

 

[ChrisM]

This is a pretty tall order for someone new to both VBA and databases, though it's not difficult once you know what you are doing.

The way I would do this is create a temp table in the database, with two fields (Article Name, Quantity). This part has nothing to do with Excel.

Set up your Excel sheet to only have two columns, Article and Quantity

Now the macro starts:

1. Using ADO, clear the temp table in the database
2. Read the two columns of Excel data into an array in VBA
3. Create an ADO recordset of the blank temp table
4. Loop through the array, adding each row to the recordset
5. Using ADO, run a SQL Update query on the database, joining the temp table with the main table you are trying to update.

You're probably wondering what ADO is. It is Microsoft's technology for working with databases in code. If you google it you will get lots of hits.

If you want to proceed with this, do a little reading on ADO. Then you're going to need a lot more detail on your database: is it definitely SQL Server? Do you have a login account with write permissions? What is the name of the table you are trying to update?

Once you have this information, you use ADO to create a connection to the database using a connection string. This is where you need to know the database type and login ID.

I'll stop there, if you want to proceed, confirm your database information and post back and we can go one step at a time.

 

[killpaddy]

Thanks for the Reply Chris. I have managed to find an excel file (with lots of VBA code) from the Exact (people who make JobBOSS) website that enters new data entered into the spreadsheet into the JobBOSS system. This probably answers your questions and solves most of the problem, I'l answer them as best I can anyway.

I think it is SQL Server, on the JobBOSS interface when switching between the Databases (There are several by the way) it describes it as 'Sql Server'.

The name of the database is 'TRAINING' in location 'TRAINING'. This is the one i have been using to test uploading. Eventually when fully tested, it will be uploaded to 'PRODUCTION' in location 'PRODUCTION'.

Yes, there are several Login Accounts and I think mine has been set with the maximum permissions


I have linked the code in a google doc below and the acctual spreadsheet below that (I cant see an 'Attach' option on this forum). Or if you give me your E-Mail I can send it accross in the proper format

https://docs.google.com/document/d/1BkTCKjrIhnGI-JsbRFrqkTyql_UGlwfOSJEFkx-bWMQ/edit (VBA Code)

https://docs.google.com/spreadsheet/ccc?key=0As_H1bFKIn8FdHBQZGgzbzI5dUlQMGZKVkhfdGhrRXc (spreadsheet)

https://docs.google.com/open?id=0B8_H1bFKIn8FWVRrRmFsRlB6dTA (document explaining how to use the spreadsheet)

I only understand bits of it, but here's basiclly what i think it does:
The code links to the last JobBOSS database accessed by whoever logs in. It 'Verifies' the information entered in each column to check its a string or integer etc. It then uploads Ive tried it and it works for entering a new material, but not for updating information on a material that 'already exists in JobBOSS'

 

[ChrisM]

This is too big a scope for a simple question and answer board. Doesn't the software vendor supply a way to update materials? If they've created their own database and you start updating records without fully understanding what you are doing you may end up with bad data.

For security, you should remove your database logon and password from the VBA code you posted.

 

[killpaddy]

Yes, I realised this when I found the spreadsheet, I hadn't thought it would be this bigger a task. I think there is an 'Update' sub in place instead of just an 'Add' sub mentioned in the code in the Class Module and is ready to run, but it doesnt seem to be set up properly yet (ive tried replacing the 'Add' sub with the 'Update' sub where it appears earlier in another Module, but it still doesnt work)

Yes, thats why im going to use the 'Training' database first for rigourous testing before applying it, if I ever work it out that is.

OK, thank you for all your advice, afew things have been cleared up and I feel I understand it a little better now after having to explain the problem and receiving your input.

I will ask on here or create a new thread if I have any queeries about smaller sections of the code

 

[Nuge440]

Thanks for the Reply Chris. I have managed to find an excel file (with lots of VBA code) from the Exact (people who make JobBOSS) website that enters new data entered into the spreadsheet into the JobBOSS system. This probably answers your questions and solves most of the problem, I'l answer them as best I can anyway.

I think it is SQL Server, on the JobBOSS interface when switching between the Databases (There are several by the way) it describes it as 'Sql Server'.

The name of the database is 'TRAINING' in location 'TRAINING'. This is the one i have been using to test uploading. Eventually when fully tested, it will be uploaded to 'PRODUCTION' in location 'PRODUCTION'.

Yes, there are several Login Accounts and I think mine has been set with the maximum permissions


I have linked the code in a google doc below and the acctual spreadsheet below that (I cant see an 'Attach' option on this forum). Or if you give me your E-Mail I can send it accross in the proper format

https://docs.google.com/document/d/1BkTCKjrIhnGI-JsbRFrqkTyql_UGlwfOSJEFkx-bWMQ/edit (VBA Code)

https://docs.google.com/spreadsheet/ccc?key=0As_H1bFKIn8FdHBQZGgzbzI5dUlQMGZKVkhfdGhrRXc (spreadsheet)

https://docs.google.com/open?id=0B8_H1bFKIn8FWVRrRmFsRlB6dTA (document explaining how to use the spreadsheet)

I only understand bits of it, but here's basiclly what i think it does:
The code links to the last JobBOSS database accessed by whoever logs in. It 'Verifies' the information entered in each column to check its a string or integer etc. It then uploads Ive tried it and it works for entering a new material, but not for updating information on a material that 'already exists in JobBOSS'

killpaddy are you still around? Did you figure this out years ago? I know this is a long shot and has been 12 years but I am working on similar approach and would like to discuss what you did.​

Thanks

Cody