Good 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.
'an Access object
Dim objAccess As Object
Set objAccess = CreateObject("Access.Appli
'open access database
OTH Production Check.accdb")
Dim strDdl As String
strDdl = "ALTER TABLE 15 - Bev Bove ADD COLUMN HR Record Point-of-Contact TEXT (255);"
Execute strDdl, dbFailOnError