Replies

That’s what I was looking for John – thanks

It wasn’t my report but let’s file this under stupid questions – I hadn’t noticed when I’d looked at it that ther was just a millimeter of spare space on the right hand side. After your reply I went back and looked – D’OHH

Thanks Charlotte,
That didn’t work but it pointed me in the right direction – this did –

SQL1 = “INSERT INTO tblLocalOption (Option_Name) VALUES (” & Chr(34) & strOption & Chr(34) & “);”

thanks

I do use the following code to link the table after I create it in the backend –
‘ Link to frontend
If Not TableExists(“strTable”) = True Then
DoCmd.TransferDatabase acLink, “Microsoft Access”, _
strDatabase, acTable, strTable, strTable
End If

The client has an already installed backend database on their server. When I send them a new front end they open a form which allows them to link to the backend. On closing this form a routine runs which updates the schema in their backend database to add any new tables, fields and indexes. This works really well in that it relieves the client of the need to make any schema changes.
The new front end does add new tables and fields and these fields and indexes are seen by the front end. The only problem I have is that I need to change the properties of some indexes to be primary and unique. So adding the follwing lines to my add table routine before I append the new index works a treat.
With idxIndex
.Unique = True
.Primary = True
End With
Thanks for pointing me in the right direction.

Thanks, I can now create fields and indexes but how do I make an index the key and how do I set the index to not allow duplicates?

OK so I’ve got it all wrong. I am trying to add a field to an existing database in the backend. So how should I be doing this. and how do I add an index? I have managed to add a table to the backend with one field though it does not have an index.

Thanks for that now if I could only solve my personal relationship probems that easily!

Thanks Hans that’s great and yes I do need to use DAO.
Regards
Kent

Thanksn Hans – That works fine but if it is run twice the backend table is deleted. What is the best way to check if the table already exists in the backend and as a linked table in case it was run from another copy of the front end?
Regards
Kent

That got it – thanks.

That got it – thanks.

Your code works but requires 4 passes to delete all the tables, I have stepped through the code and all looks OK but each time I run it it leaves some undeleted. Very strange.

Your code works but requires 4 passes to delete all the tables, I have stepped through the code and all looks OK but each time I run it it leaves some undeleted. Very strange.

WOW! that was fast – thank you.