Adding table to back end using VBA (1 Viewer)

[Design by Sue]

Can I create a table in the back end of my database using VBA in the front end? My client is using this system and I can't easily go to their location. I need to create a table on the back end and have it available in the front end applications. My thought is to create a front end only for the purpose of updating the backend with this new table. My idea is now to have it open with a button for the user to click - this will add the table to the backend with the required fields, confirm the update was completed and then close. This app then would be trashed so it would not be run again. (Though I would put a check in the VBA that if the table existed, the user would get a message box and the coding stopped to avoid overwriting the existing file.)

And once the table is created, is there a way to programmatically link it to the front end. I will be providing a new front end with this update so I could add something in the new version.

All help greatly appreciated

Sue

 

[pbaldy]

I've done that numerous times. The basics:

  Dim strSql             As String
  Dim db                 As DAO.Database
    
  Set db = DBEngine.Workspaces(0).OpenDatabase("PathToBackEnd")

  strSql = "CREATE TABLE..."
  db.Execute strSql, dbFailOnError
  Set db = Nothing

  MsgBox "New stuff added"

 

[Design by Sue]

That's very helpful - thanks. I hope to add a browse box for the user to find the backend - that should be possible correct?

and what about linking it to another front end - keep in mind that this will be run from a one time use only front end because just putting it in the back end doesn't mean it will be available in another front end, does it? OR if I do this on my work station with my working files, and create the table, then on my working version of the complete front end, link the table, will the complete front end find that table on the user's system? (sorry if that is confusing)

 

[gemma-the-husky]

you ought to be able to pick up the backend from one of the existing tables

this is a start

currentdb.tabledefs("sometable").connect

 

[pbaldy]

Yes, adding a file dialog to browse to the back end wouldn't be hard.

As to the front end, typically I'd be distributing a new front end that made use of the new table, so it would already be linked. I send the customer both the "add a table" db and the new front end, instructing them to run the utility before using the new front end.

 

[Design by Sue]

Thanks Paul and Dave - I have what I need now and have been able to get this to work. but the newly created table is not showing up in the objects window of access. I first tested it by doing the install on the front end where I was working and to see the table I had to close the database and reopen it - then it showed up in the object window. Then I created the dialog box to find the back end, and used the selection there to add the table to that back end. Again it worked but the table doesn't appear in the front end object window. When I opened the back end directly it showed up - but when I opened the front end again it is still not visible. Any thoughts what is the problem here? In the end as long as it installs, and I create the complete front end linked to it all should be fine. I just find it strange that the new table is not in the object window.

Thanks for your help!
Sue
Sue

 

[pbaldy]

Perhaps I'm missing something, but it wouldn't show up in the front end until you link to it. If you're adding it to the same db the code is in:

Application.RefreshDatabaseWindow

 

[Design by Sue]

That's the strange part - I tried first adding it to the same database and then added the programming to add it to the backend. Either way the table does not show up in the object list as I described above. It is there but not visible.

Sue

 

[pbaldy]

Did you add the code I suggested to get it to show if it's in the same db?

 

[Design by Sue]

OH - I get it - DUH! forgot the simple step of linking it. Will also test your line of code (missed that when I responded before) so I know for the future.

Always appreciate your help!

Have a good weekend.

 

[pbaldy]

Happy to help!