load access table to sql

  • Thread starter Thread starter Andre
  • Start date Start date
A

Andre

I'm trying to load an Access table to SQL 2k. I'm connecting to SQL just
fine (using ado), and emptying the SQL table just fine. But when I try to
do the following, nothing makes it to SQL:

strSQL = "INSERT INTO dbo.Enrollment( LineOfBusiness, Provider, [Group],
SortOrder#, Region, SiteIdx, Site, MM, [Month], [Year] ) " & _
"SELECT LineOfBusiness, Provider, [Group], [SortOrder#],
Region, SiteIdx, Site, MM, [Month], [Year] FROM Enrollment"
cn.Execute strSQL

Obviously, dbo.Enrollment is the SQL table, and Enrollment is the local
Access table. The statement executes just fine, but like I say, no data
makes it to the table. I tried the following, and sure enough, 1 row made
it in:

strSQL = "INSERT INTO dbo.Enrollment( LineOfBusiness, Provider, [Group],
SortOrder#, Region, SiteIdx, Site, MM, [Month], [Year] ) " & _
"SELECT '1', '1', '1', '1', '1', '1', '1', '1', 1, 2004 "

So it must be something in my Select, right?

Any help would be greatly appreciated.

Thanks, Andre
 
If cn is an ADO connection to SQL Server, then this SQL statement is being
executed by the server, right? But the Enrolment table in the Jet database
is invisible to SQL Server. As far as that cn connection is concerned, there
is only one Enrolment table, the SQL Server table. The 'cn' connection can
not see the Jet table because it is not connected to the Jet database. If
SQL Server isn't raising any error about a missing table, then I can only
imagine that it is copying records from the SQL Server Enrolment table back
to the same SQL Server Enrolment table - so if the table was empty to begin
with, it will remain empty afterward.

What to do about it? Well, you could link the SQL Server table via ODBC,
then the SQL statement can be executed by CurrentProject.Connection, which
will be able to see both records. That's probably the best solution in most
circumstances. Alternatively, you could open a recordset on the local Jet
table (using CurrentProject connection) and loop through it, adding each
record in turn to the SQL Server table through the 'cn' connection to SQL
Server.

--
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.
 
Thanks Brendan. For a table of about 75k records, which of your two options
would be the most efficient?

Andre

Brendan Reynolds said:
If cn is an ADO connection to SQL Server, then this SQL statement is being
executed by the server, right? But the Enrolment table in the Jet database
is invisible to SQL Server. As far as that cn connection is concerned, there
is only one Enrolment table, the SQL Server table. The 'cn' connection can
not see the Jet table because it is not connected to the Jet database. If
SQL Server isn't raising any error about a missing table, then I can only
imagine that it is copying records from the SQL Server Enrolment table back
to the same SQL Server Enrolment table - so if the table was empty to begin
with, it will remain empty afterward.

What to do about it? Well, you could link the SQL Server table via ODBC,
then the SQL statement can be executed by CurrentProject.Connection, which
will be able to see both records. That's probably the best solution in most
circumstances. Alternatively, you could open a recordset on the local Jet
table (using CurrentProject connection) and loop through it, adding each
record in turn to the SQL Server table through the 'cn' connection to SQL
Server.

--
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.


Andre said:
I'm trying to load an Access table to SQL 2k. I'm connecting to SQL just
fine (using ado), and emptying the SQL table just fine. But when I try to
do the following, nothing makes it to SQL:

strSQL = "INSERT INTO dbo.Enrollment( LineOfBusiness, Provider, [Group],
SortOrder#, Region, SiteIdx, Site, MM, [Month], [Year] ) " & _
"SELECT LineOfBusiness, Provider, [Group], [SortOrder#],
Region, SiteIdx, Site, MM, [Month], [Year] FROM Enrollment"
cn.Execute strSQL

Obviously, dbo.Enrollment is the SQL table, and Enrollment is the local
Access table. The statement executes just fine, but like I say, no data
makes it to the table. I tried the following, and sure enough, 1 row made
it in:

strSQL = "INSERT INTO dbo.Enrollment( LineOfBusiness, Provider, [Group],
SortOrder#, Region, SiteIdx, Site, MM, [Month], [Year] ) " & _
"SELECT '1', '1', '1', '1', '1', '1', '1', '1', 1, 2004 "

So it must be something in my Select, right?

Any help would be greatly appreciated.

Thanks, Andre
Click to expand...
Click to expand...
 
For bulk updates, executing a query will usually be more efficient than
looping through a recordset. That said, more often than not, in practice the
difference will not be so great as to be noticeable to the end user.

--
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.


Andre said:
Thanks Brendan. For a table of about 75k records, which of your two options
would be the most efficient?

Andre

Brendan Reynolds said:
If cn is an ADO connection to SQL Server, then this SQL statement is being
executed by the server, right? But the Enrolment table in the Jet database
is invisible to SQL Server. As far as that cn connection is concerned, there
is only one Enrolment table, the SQL Server table. The 'cn' connection can
not see the Jet table because it is not connected to the Jet database. If
SQL Server isn't raising any error about a missing table, then I can only
imagine that it is copying records from the SQL Server Enrolment table back
to the same SQL Server Enrolment table - so if the table was empty to begin
with, it will remain empty afterward.

What to do about it? Well, you could link the SQL Server table via ODBC,
then the SQL statement can be executed by CurrentProject.Connection, which
will be able to see both records. That's probably the best solution in most
circumstances. Alternatively, you could open a recordset on the local Jet
table (using CurrentProject connection) and loop through it, adding each
record in turn to the SQL Server table through the 'cn' connection to SQL
Server.

--
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.
Click to expand...
try
to
do the following, nothing makes it to SQL:

strSQL = "INSERT INTO dbo.Enrollment( LineOfBusiness, Provider, [Group],
SortOrder#, Region, SiteIdx, Site, MM, [Month], [Year] ) " & _
"SELECT LineOfBusiness, Provider, [Group], [SortOrder#],
Region, SiteIdx, Site, MM, [Month], [Year] FROM Enrollment"
cn.Execute strSQL

Obviously, dbo.Enrollment is the SQL table, and Enrollment is the local
Access table. The statement executes just fine, but like I say, no data
makes it to the table. I tried the following, and sure enough, 1 row made
it in:

strSQL = "INSERT INTO dbo.Enrollment( LineOfBusiness, Provider, [Group],
SortOrder#, Region, SiteIdx, Site, MM, [Month], [Year] ) " & _
"SELECT '1', '1', '1', '1', '1', '1', '1', '1', 1, 2004 "

So it must be something in my Select, right?

Any help would be greatly appreciated.

Thanks, Andre
Click to expand...
Click to expand...
Click to expand...
 
Brendan,

I finally got back to this project and have been trying to implement your
suggestion below, but I can't for the life of me find CurrentProject in
Access 97. I've poked around a bit and it seems like that might be an
Access 2000 thing. If that's the case, what is the equivalent command in
Access 97?

Oh, and you are correct about cn being an ADO connection.

Thanks again, Andre
 
Access 97 pre-dates ADO (or, at least, the wide-spread availability and use
of ADO) and has little if any built-in support for ADO. If you're using
Access 97 you'll want to use DAO instead. The nearest equivalent of
CurrentProject.Connection in DAO would be CurrentDb.

--
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.
 
Thanks again Brendan.

DAO - wasdat? :) Ok, it's been years since I've used Dao and I can't even
get a connection to my db to work. I've tried using several different code
examples from my archives but they all fail to connect to the server. Do
you have a connection snippet handy? And can you tell me what version of
the DAO library I need to include? Some of my old projects have 2.5/3.5
Compatible, and some have 3.5.

Andre
 
Andre, I answer a lot of questions. I can't remember the details of them
all. I need to see enough of the text of previous posts in the thread to
remind me of what the question was.

--
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.
 
Hope this helps...
Andre, I answer a lot of questions. I can't remember the details of them
all. I need to see enough of the text of previous posts in the thread to
remind me of what the question was.
Click to expand...

Here's your reply from this morning:
Access 97 pre-dates ADO (or, at least, the wide-spread availability and
use
of ADO) and has little if any built-in support for ADO. If you're using
Access 97 you'll want to use DAO instead. The nearest equivalent of
CurrentProject.Connection in DAO would be CurrentDb.

Here's my post from last night:
I finally got back to this project and have been trying to implement
your
suggestion below, but I can't for the life of me find CurrentProject in
Access 97. I've poked around a bit and it seems like that might be an
Access 2000 thing. If that's the case, what is the equivalent command
in
Access 97?

Oh, and you are correct about cn being an ADO connection.

Thanks again, Andre
 
Brendan,

I was able to connect using a DSN, but not DSNLess. That's fine, not
optimal, but it will work. May I ask you what the syntax would be if I'm
wanting to load a local Access table to a linked SQL table? I've tried this
but it didn't like it:

Dim rst As Recordset
Dim connect As String

connect = "ODBC;DSN=HCPHPA;"

Set wspace = DBEngine.Workspaces(0)
Set db = wspace.OpenDatabase("", False, True, connect)
CurrentDb.Execute ("Insert Into dbo_Enrollment Select * from enrollment")

The error says: run-time error 3155: odbc--insert on a linked table
'dbo_Enrollment failed.

I can't find anything on that error in the KB. Interestingly enough, I get
the same error when I try to append the local table to the linked table in a
query. I'm connected as sa to sql.

Thanks again, Andre
 
The following works for me where "dbo_authors" is an ODBC-linked SQL Server
table and "tblAuthors" is a local Jet table. I'm using Access 2003, but I
don't believe that should make any difference to this particular operation.

Public Sub Append2SQL()

Dim strSQL As String

strSQL = "INSERT INTO dbo_authors ( au_id, au_lname, au_fname, " & _
"phone, address, city, state, zip, contract ) SELECT " & _
"tblAuthors.au_id, tblAuthors.au_lname, tblAuthors.au_fname, " & _
"tblAuthors.phone, tblAuthors.address, tblAuthors.city, " & _
"tblAuthors.state, tblAuthors.zip, tblAuthors.contract FROM
tblAuthors;"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

--
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.
 
Thanks Brendan,

I'm able to load the table now using very similar code. The only issue I'm
having is because of a column named "Group" in both the local and sql table.
I've tried enclosing it in [] but that doesn't help. Any ideas what I can
do?

Andre
 
Well, I would strongly advise not using a SQL key word as a field name, but
having added the field to both tables, and deleted and recreated the link to
the SQL Server table (the new field wasn't visible in the linked table until
I did that) the only modification I had to make to the example I posted
earlier was to add the square brackets around the reference to the SQL
Server field in the SQL string as follows ...

Public Sub Append2SQL()

Dim strSQL As String

strSQL = "INSERT INTO dbo_authors ( au_id, au_lname, au_fname, " & _
"phone, address, city, state, zip, contract, [Group] ) SELECT " & _
"tblAuthors.au_id, tblAuthors.au_lname, tblAuthors.au_fname, " & _
"tblAuthors.phone, tblAuthors.address, tblAuthors.city, " & _
"tblAuthors.state, tblAuthors.zip, tblAuthors.contract,
tblAuthors.Group FROM tblAuthors;"

CurrentDb.Execute strSQL, dbFailOnError

End Sub


--
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.


Andre said:
Thanks Brendan,

I'm able to load the table now using very similar code. The only issue I'm
having is because of a column named "Group" in both the local and sql table.
I've tried enclosing it in [] but that doesn't help. Any ideas what I can
do?

Andre


Brendan Reynolds said:
The following works for me where "dbo_authors" is an ODBC-linked SQL Server
table and "tblAuthors" is a local Jet table. I'm using Access 2003, but I
don't believe that should make any difference to this particular operation.

Public Sub Append2SQL()

Dim strSQL As String

strSQL = "INSERT INTO dbo_authors ( au_id, au_lname, au_fname, " & _
"phone, address, city, state, zip, contract ) SELECT " & _
"tblAuthors.au_id, tblAuthors.au_lname, tblAuthors.au_fname, " & _
"tblAuthors.phone, tblAuthors.address, tblAuthors.city, " & _
"tblAuthors.state, tblAuthors.zip, tblAuthors.contract FROM
tblAuthors;"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

--
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.


tried
this I
get
Click to expand...
table
in thread
to be
Click to expand...
Click to expand...
 
I totally agree with your first comment, but this is an inherited db so I
have no control over naming, unfortunately.

Could this be an issue with Access 97? I've tried using and not using
SQLPassThrough and it makes no difference. I've also tried square brackets
around Group and it doesn't help either.

Here is my code, which sure looks similar to yours.

strSQL = "Insert Into dbo_Enrollment (LineOfBusiness, Provider, [Group],
[SortOrder#], Region, SiteIdx, Site, MM, Month, Year) Select LineOfBusiness,
Provider, [Group], [SortOrder#], Region, SiteIdx, Site, MM, Month, Year From
Enrollment"

CurrentDb.Execute strSQL, dbFailOnError

Any other thoughts? At this point I'm about to make a copy of the local
table, rename group and load it. It works, but is more than I want to do -
or feel like I need to do. :)

Andre

Brendan Reynolds said:
Well, I would strongly advise not using a SQL key word as a field name, but
having added the field to both tables, and deleted and recreated the link to
the SQL Server table (the new field wasn't visible in the linked table until
I did that) the only modification I had to make to the example I posted
earlier was to add the square brackets around the reference to the SQL
Server field in the SQL string as follows ...

Public Sub Append2SQL()

Dim strSQL As String

strSQL = "INSERT INTO dbo_authors ( au_id, au_lname, au_fname, " & _
"phone, address, city, state, zip, contract, [Group] ) SELECT " & _
"tblAuthors.au_id, tblAuthors.au_lname, tblAuthors.au_fname, " & _
"tblAuthors.phone, tblAuthors.address, tblAuthors.city, " & _
"tblAuthors.state, tblAuthors.zip, tblAuthors.contract,
tblAuthors.Group FROM tblAuthors;"

CurrentDb.Execute strSQL, dbFailOnError

End Sub


--
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.


Andre said:
Thanks Brendan,

I'm able to load the table now using very similar code. The only issue I'm
having is because of a column named "Group" in both the local and sql table.
I've tried enclosing it in [] but that doesn't help. Any ideas what I can
do?

Andre


Brendan Reynolds said:
The following works for me where "dbo_authors" is an ODBC-linked SQL Server
table and "tblAuthors" is a local Jet table. I'm using Access 2003,
Click to expand...
Click to expand...
but
I
Click to expand...
&
_ if
I'm
Click to expand...
enough,
I table equivalent
of might
be table
via
Click to expand...
Click to expand...
 
Well, Month and Year are both functions in VBA and therefore Access is aware of
them. Try surronding thoase two with [], also. To be really safe, you could
surround each field name with the brackets, but that is probably overkill.
I totally agree with your first comment, but this is an inherited db so I
have no control over naming, unfortunately.

Could this be an issue with Access 97? I've tried using and not using
SQLPassThrough and it makes no difference. I've also tried square brackets
around Group and it doesn't help either.

Here is my code, which sure looks similar to yours.

strSQL = "Insert Into dbo_Enrollment (LineOfBusiness, Provider, [Group],
[SortOrder#], Region, SiteIdx, Site, MM, Month, Year) Select LineOfBusiness,
Provider, [Group], [SortOrder#], Region, SiteIdx, Site, MM, Month, Year From
Enrollment"

CurrentDb.Execute strSQL, dbFailOnError

Any other thoughts? At this point I'm about to make a copy of the local
table, rename group and load it. It works, but is more than I want to do -
or feel like I need to do. :)

Andre

Brendan Reynolds said:
Well, I would strongly advise not using a SQL key word as a field name, but
having added the field to both tables, and deleted and recreated the link to
the SQL Server table (the new field wasn't visible in the linked table until
I did that) the only modification I had to make to the example I posted
earlier was to add the square brackets around the reference to the SQL
Server field in the SQL string as follows ...

Public Sub Append2SQL()

Dim strSQL As String

strSQL = "INSERT INTO dbo_authors ( au_id, au_lname, au_fname, " & _
"phone, address, city, state, zip, contract, [Group] ) SELECT " & _
"tblAuthors.au_id, tblAuthors.au_lname, tblAuthors.au_fname, " & _
"tblAuthors.phone, tblAuthors.address, tblAuthors.city, " & _
"tblAuthors.state, tblAuthors.zip, tblAuthors.contract,
tblAuthors.Group FROM tblAuthors;"

CurrentDb.Execute strSQL, dbFailOnError

End Sub


--
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.


Andre said:
Thanks Brendan,

I'm able to load the table now using very similar code. The only issue I'm
having is because of a column named "Group" in both the local and sql table.
I've tried enclosing it in [] but that doesn't help. Any ideas what I can
do?

Andre


"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
The following works for me where "dbo_authors" is an ODBC-linked SQL
Server
table and "tblAuthors" is a local Jet table. I'm using Access 2003,
Click to expand...
but
I
don't believe that should make any difference to this particular
operation.

Public Sub Append2SQL()

Dim strSQL As String

strSQL = "INSERT INTO dbo_authors ( au_id, au_lname, au_fname, " & _
"phone, address, city, state, zip, contract ) SELECT " & _
"tblAuthors.au_id, tblAuthors.au_lname, tblAuthors.au_fname, "
Click to expand...
&
_
"tblAuthors.phone, tblAuthors.address, tblAuthors.city, " & _
"tblAuthors.state, tblAuthors.zip, tblAuthors.contract FROM
tblAuthors;"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

--
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.


Brendan,

I was able to connect using a DSN, but not DSNLess. That's fine, not
optimal, but it will work. May I ask you what the syntax would be if
I'm
wanting to load a local Access table to a linked SQL table? I've tried
this
but it didn't like it:

Dim rst As Recordset
Dim connect As String

connect = "ODBC;DSN=HCPHPA;"

Set wspace = DBEngine.Workspaces(0)
Set db = wspace.OpenDatabase("", False, True, connect)
CurrentDb.Execute ("Insert Into dbo_Enrollment Select * from
enrollment")

The error says: run-time error 3155: odbc--insert on a linked table
'dbo_Enrollment failed.

I can't find anything on that error in the KB. Interestingly
Click to expand...
enough,
I
get
the same error when I try to append the local table to the linked table
in
a
query. I'm connected as sa to sql.

Thanks again, Andre



Hope this helps...

Andre, I answer a lot of questions. I can't remember the details of
them
all. I need to see enough of the text of previous posts in the thread
to
remind me of what the question was.

Here's your reply from this morning:
Access 97 pre-dates ADO (or, at least, the wide-spread
availability
and
use
of ADO) and has little if any built-in support for ADO. If you're
using
Access 97 you'll want to use DAO instead. The nearest equivalent
of
CurrentProject.Connection in DAO would be CurrentDb.

Here's my post from last night:
I finally got back to this project and have been trying to
implement
your
suggestion below, but I can't for the life of me find
CurrentProject
in
Access 97. I've poked around a bit and it seems like that might
be
an
Access 2000 thing. If that's the case, what is the equivalent
command
in
Access 97?

Oh, and you are correct about cn being an ADO connection.

Thanks again, Andre

What to do about it? Well, you could link the SQL Server table
via
ODBC,
then the SQL statement can be executed by
CurrentProject.Connection,
which
will be able to see both records. That's probably the best
solution
in
most
circumstances. Alternatively, you could open a recordset on the
local
Jet
table (using CurrentProject connection) and loop through it,
adding
each
record in turn to the SQL Server table through the 'cn'
connection
to
SQL
Server.
Click to expand...
Click to expand...
Click to expand...
 
True, but the ODBC error says "incorrect syntax near Group."

Not to worry. I took another approach and it works, and is fast too.

DoCmd.TransferDatabase acExport...

I wish I'd have discovered this a week or so ago! :)

Thank you all for your help - I really appreciate it. Hopefully it will be
a long time before I have to deal with Access 97 again!

Andre
 
Back
Top