Lost primary key

[Guest]

I have lost the primary key of 1 table. I didn't remove it myself. Have you
ever had this problem? Does this happen often?

 

[John Vinson]

I have lost the primary key of 1 table. I didn't remove it myself. Have you
ever had this problem? Does this happen often?

Could you explain what you mean by "lost"? If you open the table in
design view, is that field absent? or there but just lacking the key
icon? or what?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Hi John,
What I mean is that the key icon has disappeared from one table and as that
table had a link with another one I couldn't update any data on my form.

Thanks for helping me.

I have another question may be you can also help me. I have an excel
spreadsheet and I would like to export it to a table in Access. Can I do that
from Excel or must I do it from Access. I would like to automate this process
so that I can put it as a selection in the menu. I am quite new to Access.

 

[Jamie Collins]

I have an excel
spreadsheet and I would like to export it to a table in Access. Can I do that
from Excel

You can use ADO in Excel VBA code to do this. Querying an open
workbook is never a good idea, especially so with ADO, but the
workaround is simple: copy the relevant sheets into a new blank
workbook, save and close it, then query the closed copy. Here's some
sample code:

Sub UpdateRemoteDB()

Dim wb As Excel.Workbook
Dim Con As Object
Dim strConXL As String
Dim strConDB As String
Dim strPathXL As String
Dim strSql1 As String
Dim lngRowsAffected As Long

' Amend the following constants to suit
Const XL_WORKBOOK_TEMP As String = "" & _
"delete_me.xls"

Const XL_SHEET As String = "" & _
"MySheet"

Const DATABASE_PATH_FILENAME As String = "" & _
"C:\MyDatabase.mdb"

Const DATABASE_TABLE As String = "" & _
"MyTable"

' Do NOT amend the following constants
Const CONN_STRING_LOCAL As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH><FILENAME>;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

Const CONN_STRING_SERVER As String = "" & _
"[database=<PATH_FILENAME>;]"

' Build connection strings
strPathXL = ThisWorkbook.Path & _
Application.PathSeparator

strConXL = CONN_STRING_LOCAL
strConXL = Replace(strConXL, _
"<PATH>", strPathXL)
strConXL = Replace(strConXL, _
"<FILENAME>", XL_WORKBOOK_TEMP)

strConDB = CONN_STRING_SERVER
strConDB = Replace(strConDB, _
"<PATH_FILENAME>", DATABASE_PATH_FILENAME)

' Build sql statement
strSql1 = ""
strSql1 = strSql1 & "INSERT INTO " & strConDB
strSql1 = strSql1 & "." & DATABASE_TABLE
strSql1 = strSql1 & " SELECT * FROM [" & XL_SHEET & "$]"

' Delete old instance of temp workbook
On Error Resume Next
Kill strPathXL & XL_WORKBOOK_TEMP
On Error GoTo 0

' Save copy of worksheet to temp workbook
Set wb = Excel.Application.Workbooks.Add()
With wb
ThisWorkbook.Worksheets(XL_SHEET). _
Copy .Worksheets(1)
.SaveAs strPathXL & XL_WORKBOOK_TEMP
.Close
End With

' Open connection to temp workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = strConXL
.Open
.Execute strSql1, lngRowsAffected
.Close
End With

Debug.Print lngRowsAffected

End Sub

If you prefer GUI tools you could use MS Query from Excel, however it
is not as user friendly for anything other than SELECT queries. Easier
if you create a stored procedure in the database and pass values as
parameters.

Jamie.

--

 

[John Vinson]

Hi John,
What I mean is that the key icon has disappeared from one table and as that
table had a link with another one I couldn't update any data on my form.

That's VERY wierd. I'd suggest the following:

- Open the relationships window, and click the "view all" icon. If
there are any relationships from this table's primary key, select the
join line and delete it.
- Open the table in design view. Click the Indexes icon (looks like a
lightning bolt hitting a datasheet) and see if there is an index named
Primary Key. If there is, delete it (you'll be recreating it).
- Compact the database (Tools... Database Utilities... Compact and
Repair).
- Open the table in design view again, select the field, and click the
key icon to recreate the Primary Key index.
- Reestablish your relationships.

I *really* don't think Access would just spontaneously drop a primary
key. You might have some database corruption, or possibly someone else
has been meddling.

Thanks for helping me.

I have another question may be you can also help me. I have an excel
spreadsheet and I would like to export it to a table in Access. Can I do that
from Excel or must I do it from Access. I would like to automate this process
so that I can put it as a selection in the menu. I am quite new to Access.

I'm very glad Jamie was here to answer that one... I learned
something!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Brendan Reynolds]

The only situation I've seen where an index has gone AWOL like that happened
when a database was corrupted and had to be repaired. The repair process
completed without any error messages or warnings, but afterwards one index
no longer existed.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.