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

killpaddy

Board Regular
Joined
Jul 31, 2012
Messages
52
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

Board Regular
Joined
Jul 31, 2012
Messages
52
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

Board Regular
Joined
Jul 31, 2012
Messages
52
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
 

Forum statistics

Threads
1,089,437
Messages
5,408,214
Members
403,190
Latest member
RBrite

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top