Indexing strategy and code with there is a one to one relationship

[mountainclimber]

What happened? Error message??

Here is a M$oft example

I beat you to it. That is how I created the Alter Table statement in the first place: by copying that link. See my recent post.

Any ideas why my alter table statement isn't working?

[June7]

Has Ticker field been defined as PK in tbl0?

That links says ADD CONSTRAINT is to 'add a multiple field index', has nothing to do with associating PK/FK data. Eliminate that clause and see what happens.

I still don't understand how the FOREIGN KEY and REFERENCES clauses will 'know' which Ticker from tbl0 goes with which record in tbl1 thru tbl5 and correctly populate field.

[June7]

Can build relationships in Relationship Builder if you want. This means when you create a query, the link will automatically establish.

[mountainclimber]

Has Ticker field been defined as PK in tbl0?

That links says ADD CONSTRAINT is to 'add a multiple field index', has nothing to do with associating PK/FK data. Eliminate that clause and see what happens.

I still don't understand how the FOREIGN KEY and REFERENCES clauses will 'know' which Ticker from tbl0 goes with which record in tbl1 thru tbl5 and correctly populate field.

I tried this:

db.Execute "ALTER TABLE tbl1 " _
        & "FOREIGN KEY (TickerID) REFERENCES tbl0 (Ticker);"

...and I get:
"Syntax error in ALTER TABLE statement.

[mountainclimber]

Can build relationships in Relationship Builder if you want. This means when you create a query, the link will automatically establish.

I need to do this in VBA every day.

[June7]

Do what in VBA every day - build relationships in Relationship Builder?

[mountainclimber]

Do what in VBA every day - build relationships in Relationship Builder?

I take in data that changes, determine data types, create schema.ini file, divide data up, import it, create tables and relationships.

[June7]

Why would you have to create new tables as opposed to appending to existing tables? Why would data structure be dynamic? Not all from the same source? If this is not related data, why all into one database file?

[mountainclimber]

Why would you have to create new tables as opposed to appending to existing tables?

Because, for now, each table represents a date. I eventually drop these files and use a time series table.

Why would data structure be dynamic?

Because users have the ability to change the source data and what it looks like. I am handling this with a schema.ini file in the same directory.

Not all from the same source? If this is not related data, why all into one database file?

Because opening Excel files each time I want to access data is super slow. What alternative do you suggest? There is some related data, but 95% of it is not.

[June7]

Users change data source and its structure? Never encountered such a situation. If I understand, you want to programmatically build a database file from scratch. Sorry, no experience with what you are attempting. No idea how to use schema.ini file.

[orange]

@mountainclimber

You really have to pin down the scope of the issue before you, and get management or whoever has given you this to resolve, to sign off with the scope/requirements. Relational database is not the tool if you have a different table definition every day. As they say, if you can't describe/define the issue, then nobody can build it.

If the requirement really is this variable, then you need to train others in ad hoc database retrieval and programming. You also may want to do some analysis of the source and format and need of this information.

You need a clear set of requirements in plain English. From there you can do some analysis and come up with some options. You may want to research relational database design just to refresh some concepts.

You may find you need some sort of template that can be driven from a series of inputs to create the structure/commands you need to do the job. But readers are not nearly as familiar with your set up and needs as you are. And we are still guessing at how most of what you have said fits together and could be resolved or supported with Access.

Good luck.

[mountainclimber]

@mountainclimber

You really have to pin down the scope of the issue before you, and get management or whoever has given you this to resolve, to sign off with the scope/requirements. Relational database is not the tool if you have a different table definition every day. As they say, if you can't describe/define the issue, then nobody can build it.

If the requirement really is this variable, then you need to train others in ad hoc database retrieval and programming. You also may want to do some analysis of the source and format and need of this information.

You need a clear set of requirements in plain English. From there you can do some analysis and come up with some options. You may want to research relational database design just to refresh some concepts.

You may find you need some sort of template that can be driven from a series of inputs to create the structure/commands you need to do the job. But readers are not nearly as familiar with your set up and needs as you are. And we are still guessing at how most of what you have said fits together and could be resolved or supported with Access.

Good luck.

Thank you. This effort it super complex and ultimately proprietary. There is way too much to it to explain here; however, I do have it scoped out. I work for a hedge fund. I just ask specific technical questions that I hope stay narrow. Sometimes that doesn't workout....like here. Thanks all.