• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 167
  • Last Modified:

Assistance with creating workable VBA code to alter Excel linked tables in Access

Error received from VB when I submit my codeSnippet of the linked Excel tables I need to update in AccessError received from VB when I submit my codeGood day to all! I am literally at wit's end with attempting to create a VBA code that works to update linked Excel linked tables in Access. Let me provide some background. Attached is a screenshot of just SOME of the linked Excel tables in Access that I need to create a new field for. There are a total of 40 Groups and at 11 SELECT tables per group, you do the math that is A LOT of tables to update manually (440= 11 x 40), that is not only cumbersome, but it is painful! There has to be a more automated way!

Naturally, I would have submitted an ALTER command with ADD Column, and be done with it! But as you may know, in Access 2002, MS took away the benefit to modify linked Excel tables. So that door closed on me.

I have turned to VBA. Not that great at it, SQL is more my thing, but apparently there is literature out there to perform such task, the one I am trying to do via VBA. And as I am getting more exposed to VBA, I am getting the gist of it. I believe I created a VBA code that will do the job (see below), but when I perform the VBA, I get  Run-time error '2147217900 (8004e14):
Syntax error in ALTER TABLE statement.
Now what am I doing wrong? Can someone please help? Further, once this problem is involved, can someone please show me how to do mass ALTER TABLE statements within VBA, please? That way I can get through this painstaking task? :-( In SQL, I would have been able to do the mass statements within the ERP, but Access' limitation is that you can only do one ALTER statement at a time, which I don't understand. But anyway, thank you for your help! Be Blessed

P.S. It is worth mentioning, the ideal scenario is once I add the column to each linked table, the connections are already in place that when I hit refresh on these tables in Excel, the data will feed over. So I would like to keep it that way. No breaking the link and then relinking or converting to local table and then export back out.

Sub ThankYouJesus()
'an Access object
Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")
'open access database
Call objAccess.OpenCurrentDatabase( _
"D:\Work\DuPont\Exercises\Exercise4\OTH Production Check.accdb")
Dim strDdl As String
strDdl = "ALTER TABLE 15 - Bev Bove ADD COLUMN HR Record Point-of-Contact TEXT (255);"
CurrentProject.Connection.Execute strDdl, dbFailOnError
End Sub
0
Kelvin Rance
Asked:
Kelvin Rance
  • 6
  • 4
  • 3
6 Solutions
 
PatHartmanCommented:
Even during the period where you could update data in linked tables, you could NEVER alter the structure.  

To update your spreadsheet links, replace the spreadsheets with updated versions and relink them.

If what you are asking to do is to automatically change all the linked spreadsheets to add the new columns because they are not actually in the spreadsheet, you can do that but you'll need to use OLE automation to add the columns.  Then you'll need to close the spreadsheet and refresh the link.

I might add that keeping your data in Excel is a poor design choice.

PS you can sort of do this with linked databases but you have to get the DDL statement to run in the actual database you want to alter.  The point is that you cannot change the schema externally, you have to do it internally and so OLE is what you would use to change the actual spreadsheet.
0
 
Kelvin RanceAuthor Commented:
@PatHartman- Thank you for your response. I must admit, I was somewhat confused by your response, because setting up DDL statements was  what I attempting to do via VBA. Are you suggesting some place else that I should run these DDL statements? If so, where and how?

And yes, I am asking to automatically change all the linked tables to reflect the new columns in them through a refresh in Excel on the connection, which is happening when I make the change to the SELECT statement in Access to the specific spreadsheet. But again, this is manual and I would want want to automate this in Access, so when I get to Excel, I just need to hit "refresh" and the change done in Access will show in Excel

Does that make sense?
0
 
Fabrice LambertFabrice LambertCommented:
Hi,

linked excel file work great when reading data with SQL statements (SELECT statement), but give major troubles when you need to update the data or data structure.

The only way I can think of is to:
- Import you data into a temporary table.
- Manipulate the temporary table.
- Break the link.
- Export you data to excel (overwriting the old file).
- Rebuild the link.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Kelvin RanceAuthor Commented:
@Fabrice Lambert:

Good day! Thank you for your input! You are correct in your response. There is that way, but that way is inefficient in my opinion. It would actually be faster for me to go to each linked table and edit the query's SELECT statement. In looking at efficiency, VBA appears to be the way to go.

The knowledge is out there. Someone knows how to get my VBA code to work. But now it is a matter of that person viewing my question and be willing to share their knowledge for getting this code to work. I will continue trying on my end until then. Be Blessed
0
 
Fabrice LambertFabrice LambertCommented:
All that is doable with VBA
Const srcTbl As String = "15 - Bev Bove"
Const tmpTbl As String = "tmpTable"
Const filePath as String = "c:\..........."
Dim db As DAO.Database
Dim strSQL as string

strSQL = vbNullString
strSQL = strSQL & "SELECT *" & vbcrlf
strSQL = strSQL & INTO " & tmpTbl & vbcrlf
strSQL = strSQL & "FROM [" & srcTbl & "];"

    '// import data into a temporary table
Set db = CurrentDb
db.execute strSQL, dbFailOnError

    '// delete the link
db.TableDefs.Delete srcTbl
db.TableDefs.Refresh

'// manipulate your datas here

    '// export the data
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, tmpTbl, filePath , True

    '// rebuild the link
docmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, srcTbl, filePath , True

Set db = Nothing

Open in new window

Don't forget an error Handler in case Something goes wrong.
0
 
PatHartmanCommented:
DDL isn't the answer because DDL works only against relational databases and Excel is certainly not a relational database.  Using OLE automation, your code would open the spreadsheet, insert the column, save the spreadsheet, and finally relink it.

Are all the spreadsheets in one directory or will you have to specify the location for each one?  Are all the spreadsheets linked to the same FE or are they linked to different FEs?

Using the method suggested by Fabrice, you would run your DDL statement on the temp table after you import the data.  Of course this method assumes that the spreadsheets are just tables and have only a single sheet.  If they contain macros or multiple sheets, OLE automation is your only choice.  

I don't have any code to offer since I would never do this but if you are not familiar with automation code, you can actually get Excel to do a lot of the work for you.  Open the spreadsheet you want to modify and turn on the macro recorder.  Go about making the change.  Then turn off the recorder and look at the generated code.  With minor modifications, you can use the generated VBA in your Access app to perform the same action on other spreadsheets.
0
 
Kelvin RanceAuthor Commented:
@PatHartman @Fabrice Lambert

I want to thank you both tremendously for your continuance input! You two have been the only commenters and I really appreciate your help! As an update to this thread, I do want to share that in tinkering with the VBA, I managed to clear up the Syntax error first described (thank God)!

Apparently, with not including brackets ("[") around the table and field, produced the error.  However, I am still not there yet. When I provide brackets around the table and field, now I get that error I get when I attempt to alter via SQL, "cannot execute data definition statements on linked data sources", so to your point Pat, OLE automation is the way to go.

Fabrice's way would work, but because this is a workbook with multiple worksheets/tabs, that method won't work in this instance, but it is useful to have for future instances. So again to your point Pat, OLE automation is the way to go.  Understanding which tool will work in this instance is half the solution. The other half of the solution is crafting an automation specific to my need.

I have been exposing myself to more of this automation and found it to be REALLY cool with what you can do with it! Additionally, I have specifically been looking for direction to how to incorporate DDL into Excel automation and found one site that could provide such direction. From Software Solutions Online, (http://software-solutions-online.com/vba-add-fields-to-external-access-database-automation/), it is described how to add fields to external database via automation. However when I run the VBA from the given example (and of course adapting the values to my specific values) and still receive the attached error.  Any idea why? Be Blessed
VBA-Error-screenshot-update-11.18.20.PNG
0
 
Kelvin RanceAuthor Commented:
@PatHartman

Relooking at your response, I realized you asked additionally questions that I apologize I did not answer. All the spreadsheets are in the same directory. I do not know what FE stands for. What does FE stand for?

Additionally another question for you (or anyone else), once I craft the Excel automation to perform the task, and I plow into Access do the function, will that also take care of create the new column in Access?

Remember, currently I am manually updating each SELECT statement in SQL view in Access, to add the new column. Once I refresh the linked table in Excel, the data feeds over to Excel and I see the new column in the Excel workbook. I am trying to avoid doing that manually for 400+ tables. I want to automate that and it be done in a batch (i.e. I perform a code and multiply that code for all the tables in one swoop). Make sense?

Additionally, I am not sure if this matters, but we tossed around the word "table" around. To be clear, the data from Access into Excel is coming over as a table. I had it setup that way. Does that open the DDL route now or still OLE automation?
0
 
PatHartmanCommented:
I see that you are fixated on DDL but it will not work for you.  The link is for using DDL against an "ACCESS" database FROM Excel.  You want to go the other way and since Excel is NOT a table, you cannot use DDL to modify it.

Once you figure out the automation code you need to modify the workbook, you need to refresh the link.  That will make Access "see" the new column.

If your select statements select specific columns rather than Select *, they will each need to be modified.  Good practice is to select each column specifically but in this case, its going to cost you.

It may turn out to be simpler to use Fabrice's suggestion to import the data from the spreadsheet, use the DDL on the local Access table and then write the new table back to the spreadsheet.  That requires the minimum amount of automation code.

FE = Front End.  BE = Back End.  The FE is the "application" part which is the forms, reports, code, and macros.  The BE is the data which is normally Jet/ACE or some relational database such as SQL Server.  

I strongly suggest you rethink using Excel as the BE.
0
 
Kelvin RanceAuthor Commented:
@PatHartman:

Once again, I thank you sincerely for your continue responses. I was afraid that would happen (adding columns on Excel, does not take away the need to manually go to each linked table and edit the SELECT statement), SELECT statements are pulling specific columns, so no SELECT *. Also, I am not sure how to answer whether Excel is a FE or BE, but thank you for providing education on the difference. Based off your reading of my response, I will let you tell me what you think it is.

After, reading your response,  it had me thinking, "Did  I properly communicate the present schema?" I have a feeling, I did not. And so my apologies. I feel that once I say it differently, yours and Fabrice's response will be different from what it is (at least I hope). But if the response remains the same, then at least I know i explained it correctly. So just for my easement, I am going communicate it differently. In addition, I will provide illustrations.

Presently the Excel workbooks are linked to Access. However the Access table for which the Excel workbooks are pulling from are from the same table but found in another db via linked table manager. To not confuse things, I will give labels:

  "15 - Bev Bove" (for example) is in her own individual workbook with a tab labeled 15. The data in her table is coming from an Access table called Production. Production table is in a db called Production Check. But the same Production table is being linked to the source Production table found in a db called Our OTH Reports. See attached pictures..

When I edit the SELECT statement to include the new column in Access manually, and refresh the link in Excel, I see the new column in Excel and the queried data from Access. However I don't want to do this manually. I want to automate that and if OLE automation or temp table is way to do this, so be it. I just want to minimize the cumbersomeness that manually editing each SELECT statement in each linked table is creating.

Does that make sense now? Is that how you interpreted it initially?

I definitely do agree that this could have orchestrated better, but this is not my project to make drastic changes. I am just maintaining it. When the time comes to do this project from scratch, I will be sure to reach out to you to determine what is a better way to do this in the future.

P.S. When looking at the path of the Excel screenshot, you will find a different path than what you will find in the Production Table-Linked Table Manag...screenshot. Please ignore. The different paths speak to a local copy and the shared drive. The G:\path is the local copy for which I took from the network and placed on my personal drive. For the workbooks, I did not change the path to point to the local copy. The purpose is, I would do my testing on the local copy and if it works there, then I would apply to the network drive version without disturbing the network shared version with all this testing.
AFTER-SELECT-Statement.png
BEFORE-SELECT-Statement.png
Production-Table--Linked-Table-Manag.png
Excel-Connection.PNG
0
 
PatHartmanCommented:
Does that make sense now? Is that how you interpreted it initially?
No.  From your description, it sounded like the data is physically in the spreadsheet and Access is linked to Excel.
15 - Bev Bove" (for example) is in her own individual workbook with a tab labeled 15
Horrible design.  You are forced to modify the application every time you add a new "person" and you are forced to modify the design of every object if you want to change a table.  I don't see any advantage to using Access when you make it act like a spreadsheet.  Just be glad it wasn't you who designed this abomination.  Do you also need to make changes to the spreadsheet to accommodate the new column?  Change formulas? Add totals?

Is Access the "master" or do you also update this data in the Excel spreadsheet?  

A better solution is to link the spreadsheet to the underlying table rather than individual queries and create the queries in the workbook.  Each workbook would have a custom query that selects only the person you want.  You might as well use Select * unless you really don't want all the columns.  At least this way, changing the table schema would only mean refreshing the link from the spreadsheet.  It also means that you only have to create a a new spreadsheet if you add a new person.  This moves all the change actions to the spreadsheets where they belong because using Excel is the cause of the problem.

Your predecessor created a pathological connection between Access and Excel and it isn't a pretty sight.

Whatever application holds the data is the BE.  Whatever application displays and manipulates the data is the FE.  You should have one Access database that operates as the FE and a separate Access database that operates as the BE.  Access apps should ALWAYS be split.  The BE database is stored on a network share and each user has a separate copy of the FE stored on their local hard drive.  In my case, I use a shortcut connected to a batch file to open the FE application.  The batch file copies the "master" copy of the FE from the server to the local PC and then opens it.  This gives the user a fresh copy of the FE each time he runs the shortcut.  Because the FE is linked to the BE, all users see the same data.  All it takes is criteria to limit the data returned so you could have exactly the same setup with much less pain by simply using Access as the FE.  If you don't want to purchase Access licenses for all users, they can use the FREE Access runtime engine and only the developer needs a licensed copy of Access.
0
 
Kelvin RanceAuthor Commented:
Good morning to all! I want to give you an update on this question by first saying, "When God wants to receive  glory on a matter, He will make the impossible possible!" I say that because, by His grace, He showed me how it could be possible to achieve what I have been asking to have done, when it was thought to be impossible!

If you recall, I posted the question for how I could alter linked Excel queries using DDL language in a batch, because I had 440 tables to alter. Only two commenters: PatHartman and Fabrice Lambert rose to challenge and provided some insightful and helpful strategies, for which i thank them both for! But in this instance, there is a strategy far more suitable!

You can change the query makeup in VBA. By changing the query's makeup the VBA, you have effectively done the same thing I was doing manually. And the best part is you can do this change in DDL and in a batch, which is what I did! Once I did, I was done with my task quickly!

I hope this helps someone else out!

@Fabrice Lambert and @Pathartman- Thank you both for your insight and assistance! I hope to work with you both in the future! Be Blessed
0
 
Fabrice LambertFabrice LambertCommented:
OP forgot to pickup a solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now