Creating a table in the backend Database with vba (1 Viewer)

[pacctono]

Hello, people!

I have been looking into google and I have not found it.

I have an access database file that I am using as backend with the main (or shared) tables and the local one as frontend. Now I would like to create a table, only the structura, in my backend database. I used <Access.Application.DoCmd.CopyObject>, but It copies the whole table (structura plus data) I would, just, want to create the empty table. I deleted the data after creating it but I hope there is a command(s) that just created the empty table.

Beside that I do not know why but for each table created (and deleted the data), windows 10 opens an access section.

Anybody can help, please?

 

[pacctono]

I found my answer:

SELECT * INTO <TABLENAME> IN '<RUTA DE LA BASE DE DATOS BACKEND>'
FROM <TABLENAME>
WHERE 1=0;

I still, do not know why for each table created (and deleted the data) using <Access.Application.DoCmd.CopyObject>, windows 10 opens an access section.

 

[The_Doc_Man]

Have you looked into DDL? (DDL = Data Definition Language) There is a statement, CREATE TABLE, that lets you define a table and fields. You can use it with Access SQL. Not to say that your solution is wrong in any way (and in fact is probably a LOT easier). But the technically correct way would be to use DDL to build a new table.

CREATE TABLE statement (Microsoft Access SQL)

Office developer client VBA reference documentation

docs.microsoft.com

If you browse in the area of that link (hint: Use the list on the left to explore topics) you would find ways to perform this task. You would have to open a database object to the backend as a way to qualify WHERE you wanted to build the table.

 

[Pat Hartman]

You would never be creating tables using VBA or DDL in normal situations. I use DDL as Doc suggested when I create a database that is used to update my client's BE If I have to update his FE and BE. This is an unusual setup. When the BE is under your control, you would normally create objects by using the GUI because they wouldn't be created on the fly.

 

[bastanu]

If you are "pushing" a table from the front-end to the back-end you might find my free utility here handy:

FBA FE to BE Table Push - ForestByteApps

ForestByteApps - A place for Microsoft Access code samples and utilities to make your life easier. Free Microsoft Access tools and sample VBA code

forestbyte.com

Cheers,

 

[Pat Hartman]

My assumption when someone asks this question is that they are doing this for the wrong reason because usually people don't get to the point of building tools for their own convenience until they have mastered building an application and so are usually sufficiently experienced to work it out for themselves.

@pacctono If you are trying to build a tool for yourself, then creating objects using code is fine. That's what tools are all about. But, when you are building an application for some business purpose, it is almost always wrong to build objects on the fly. Certainly tables do not get added to an app on the fly because users should never have access to tables and if there are no forms/reports to support those tables because you have not built them, then there is something wrong with this picture.

If you'd care to share the reason for the request, we can probably offer a different solution that will work better in business application for the user community.

Even if I were building an app for my own personal use, I would never do this. I did explain the one use I do have for this technique which is to convert a user client's BE to an updated version which is needed by a new FE release. So, I'm not saying you would never have occasion to create tables on the fly but I'd look very closely at the requirement.

I also have clients that need to download large files that they then want to use in Access apps I have created for them. To avoid the db bloat issue that occurs when you delete/add data repeatedly, I create a separate BE for this downloaded data. The template has tables and indexes defined but is other wise empty. Each day, the user does the download to get new data and the app copies the empty template and overwrites yesterday's version. It then imports the downloaded data and all the queries just work because no names have changed. No bloat occurs because the template is replaced each day or each time the data needs to be refreshed.

 

[Galaxiom]

If you are trying to build a tool for yourself, then creating objects using code is fine. That's what tools are all about. But, when you are building an application for some business purpose, it is almost always wrong to build objects on the fly.

+ 1. My basic rule is that a database application must be able to work in Runtime. That means no new fields, let alone tables. Capabilities are expanded by adding records, not objects.

 

[pacctono]

My assumption when someone asks this question is that they are doing this for the wrong reason because usually people don't get to the point of building tools for their own convenience until they have mastered building an application and so are usually sufficiently experienced to work it out for themselves.

@pacctono If you are trying to build a tool for yourself, then creating objects using code is fine. That's what tools are all about. But, when you are building an application for some business purpose, it is almost always wrong to build objects on the fly. Certainly tables do not get added to an app on the fly because users should never have access to tables and if there are no forms/reports to support those tables because you have not built them, then there is something wrong with this picture.

If you'd care to share the reason for the request, we can probably offer a different solution that will work better in business application for the user community.

Even if I were building an app for my own personal use, I would never do this. I did explain the one use I do have for this technique which is to convert a user client's BE to an updated version which is needed by a new FE release. So, I'm not saying you would never have occasion to create tables on the fly but I'd look very closely at the requirement.

I also have clients that need to download large files that they then want to use in Access apps I have created for them. To avoid the db bloat issue that occurs when you delete/add data repeatedly, I create a separate BE for this downloaded data. The template has tables and indexes defined but is other wise empty. Each day, the user does the download to get new data and the app copies the empty template and overwrites yesterday's version. It then imports the downloaded data and all the queries just work because no names have changed. No bloat occurs because the template is replaced each day or each time the data needs to be refreshed.

Sorry, it took so long to answer you.

You wrote>

"If you'd care to share the reason for the request, we can probably offer a different solution that will work better in business application for the user community."

I am making an accounting system. Every year ( just one time on a year). I have to initialize (create) a new account table (mainly, two columns code and description). For how our way of life is I have to create it. So I have a form where the user add some values and when he (or she) is ready press the "Ok" button that runs the vba code to initialize the year, including creating the account table.

 

[Galaxiom]

Every year ( just one time on a year). I have to initialize (create) a new account table

That is exactly the kind of wrong reason Pat was talking about.

It should all be in the one table with a field to designate the year, either by using a date or simply the year as a number. The rollover process should update the Default. Queries should include the year so that only the current year, or a year entered a form with the default as the current year, to select the records.

 

[Pat Hartman]

I'm not sure why your chart of accounts changes every year. There is some deeper problem that we don't understand. I'm no subject matter expert but I've built and used a number of accounting systems and none of them ever needed a new chart of accounts each year. It feels like you are embedding the year in the account name but the year belongs in your transactions. Can you clarify why you need the new chart of accounts each year?

If the accounts change over time, the standard method would be to mark them as inactive (usually with a date rather than just a flag) but otherwise leave them in the chart for reporting purposes. The Data entry form, would check the active flag when a transaction is entered and not allow new entries for accounts that are marked as inactive.

 

[pacctono]

I'm not sure why your chart of accounts changes every year. There is some deeper problem that we don't understand. I'm no subject matter expert but I've built and used a number of accounting systems and none of them ever needed a new chart of accounts each year. It feels like you are embedding the year in the account name but the year belongs in your transactions. Can you clarify why you need the new chart of accounts each year?

If the accounts change over time, the standard method would be to mark them as inactive (usually with a date rather than just a flag) but otherwise leave them in the chart for reporting purposes. The Data entry form, would check the active flag when a transaction is entered and not allow new entries for accounts that are marked as inactive.

I got your point and I am agree. I live in Venezuela and sometime is difficult for people out of our country to understand our economy. In three years we have changed our coin two times. If I had trillon bolivares in july, 2.018; now I have 10 bolvares. Our zeros to the right does not have any value, jejejejejeje. And the government is talking to use another type of coins.

That does not have anything to do with an accounting system. But I want you to think about our economy.

NOTE: I was practicing french and I explianed a friend that live in France how difficult was to get dollars from the bank and she did not understand me. I thought it was my french, so I went to my french teacher, and she told me your french is good but nobody outside Venezuela will understand what is going on here with the economy.

 

[Pat Hartman]

I understand. Our government is trying to turn the US into Venezuela because Socialism works so well. It is hard to get back your government once it has been stolen from you Now we've taken to using the police powers of the state to persecute the former President. You should probably join in to our political discussions so you can tell the rest of the world first hand what it's like to live under Socialism.

Back to the question. Did our answers help? I guess the floating currency makes it hard to do year over year reporting. Maybe you need to adjust your history each time the currency changes so that you are comparing apples to apples. You could use a separate chart of accounts but I would mark the previous items inactive as I suggested above rather than adding a new table.

 

[CJ_London]

It is possible that @twgonder can give you some ideas - he’s based in Columbia I believe and trying to deal with multiple currencies. The @ in front of his name means he will get a notification

 

[pacctono]

I understand. Our government is trying to turn the US into Venezuela because Socialism works so well. It is hard to get back your government once it has been stolen from you Now we've taken to using the police powers of the state to persecute the former President. You should probably join in to our political discussions so you can tell the rest of the world first hand what it's like to live under Socialism.

Back to the question. Did our answers help? I guess the floating currency makes it hard to do year over year reporting. Maybe you need to adjust your history each time the currency changes so that you are comparing apples to apples. You could use a separate chart of accounts but I would mark the previous items inactive as I suggested above rather than adding a new table.

It helped. Thanks! Muchas gracias.

 

[Pat Hartman]

De nada

 

[GPGeorge]

I got your point and I am agree. I live in Venezuela and sometime is difficult for people out of our country to understand our economy. In three years we have changed our coin two times. If I had trillon bolivares in july, 2.018; now I have 10 bolvares. Our zeros to the right does not have any value, jejejejejeje. And the government is talking to use another type of coins.

That does not have anything to do with an accounting system. But I want you to think about our economy.

NOTE: I was practicing french and I explianed a friend that live in France how difficult was to get dollars from the bank and she did not understand me. I thought it was my french, so I went to my french teacher, and she told me your french is good but nobody outside Venezuela will understand what is going on here with the economy.

Lo siento. Venezuela de hace 25 años y mas era un país muy precioso. La ultima vez que fuimos estaba justo ante de Chavez, 1999 creo.

Of my ex-wife's family born and raised there, roughly half of the generation between 20 and 50 have now emigrated to Chile, Canada, Italy and the US. Only the very young and the older generations have stayed. One of her brothers died a few years ago because he could no longer get his medications. Not that he couldn't afford it, but that it was no longer available to "ordinary people" at any price.